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?

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.


=INDEX(arraytable,MATCH(E2,Data!C1:C5185,0),5)

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!
posted by Ruthless Bunny to Computers & Internet (5 answers total) 3 users marked this as a favorite
 
how about

=CHOOSE([function that returns 1, 2 or 3 depending on which variable was entered*],indexmatch1,indexmatch2,indexmatch3)

?

*you could do this with IF statements, but there are probably more elegant ways.
posted by milestogo at 1:06 PM on May 31, 2012


You don't want your OR statements inside the INDEX/MATCH formulas. You want three parallel INDEX statements, nested in IF statements. Then you select the first one that doesn't produce an error.


=IF(NOT(ISERROR(
INDEX(arraytable,MATCH(E2,Data!C1:C5185,0),5), INDEX(arraytable,MATCH(E2,Data!C1:C5185,0),5),
IF(NOT(ISERROR(
INDEX(arraytable,MATCH(F2,Data!C1:C5185,0),5), INDEX(arraytable,MATCH(F2,Data!C1:C5185,0),5),
INDEX(arraytable,MATCH(G2,Data!C1:C5185,0),5), INDEX(arraytable,MATCH(G2,Data!C1:C5185,0),5)))))))


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.


Currently running on a Linux box, so I can't test the statements.
posted by IAmBroom at 1:06 PM on May 31, 2012


Sorry, some of your question got pasted in as junk after the equation block above.
posted by IAmBroom at 1:07 PM on May 31, 2012


You don't need an OR statement
Replace the part of your formula where you have E2 with this: IF(E2="",IF(F2="",G2,F2),E2)
so =INDEX(arraytable,MATCH(E2,Data!C1:C5185,0),5) becomes
=INDEX(arraytable,MATCH(IF(E2="",IF(F2="",G2,F2),E2),Data!C1:C5185,0),5)

E2 takes priority, but if it is blank, the formula looks at F2 and then at G2.
posted by soelo at 1:08 PM on May 31, 2012 [1 favorite]


Response by poster: Yay!

Thank you so much!
posted by Ruthless Bunny at 1:22 PM on May 31, 2012


« Older First-time Online PC Order   |   Where to find the best sandwiches in Atlanta, GA? Newer »
This thread is closed to new comments.