Using formulae when I should be using VBA
May 31, 2012 1:00 PM Subscribe
Excel 2007 Filter: Is it possible to nest 3 OR
statements in an Index and Match formula?
posted by Ruthless Bunny to Computers & Internet (5 answers total) 3 users marked this as a favorite
I am working on a spreadsheet that needs to return a Key Number based upon one of three criteria a person can enter on the sheet.
I can get this Key Number from one of three pieces of data: Customer Name, ID-1 or ID-2. So I've got a table with the Key Number, Customer Name, ID-1 and ID-2. If someone puts ONE of these three items in, I want the Key Number returned.
I know how to write the formula for matching ID-1, but I'm baffled by how to nest the three OR statements inside a Index and Match Formula.
So if someone puts ID-1 in Cell E2, then it can find the Key Number in column 5 of the arraytable-(this is the named range).
What I need is if someone puts ID-2 in Cell F2, to find the Key Number in column 5, OR if someone puts Customer Name in Cell G2, to find the Key Number in column 5.
The data for ID-2 is in cell range Data!d2:d5185 and the data for Customer Name is in cell range Data!e2:e5185.
So I need the syntax to nest the Or statements.
And of course, if any of the cells in column E, F or G is empty, I want an empty cell in column A (those #N/A look so nasty.)
Can anyone point me in the right direction? My Google-Fu is failing me!