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

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,

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!

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

=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

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

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

This thread is closed to new comments.

=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