2 or more Index Match Searches in one?

[nobodyukno]

Ok here's my formula:

=IF(ISERROR(INDEX(OFStats!$O$51:$O$93,MATCH(U6,OFStats!$B$51:$B$93,0))),"",INDEX(OFStats!$O$51:$O$93,MATCH(U6,OFStats!$B$51:$B$93,0)))

So that takes U6 as a reference and looks through B51:B93 on another sheet for that reference and if it finds that reference, it goes to column O and takes the value from there. Works well.

However, I want it to search more values.

For example,
If the formula doesn't find the reference in B51:B93, then I want it to do the same thing but search cells B1471:B1513 and see if it finds the reference there. And if it does, do the same thing as the formula above, and go to column O and take the value from there.

Pretty much the same formula, except if it doesnt find the reference in the first lookup section, then it goes to another section.

[nobodyukno]

I'm sure its simple. I did + and the formula again but that adds things up wrong I think?

[tigeravatar]

nobodyukno,

Do you have to make it range specific? Using whole columns on a vlookup should accomplish what you're looking for...
=IF(COUNTIF(OfStats!B:B,U6)>0,VLOOKUP(U6,OfStats!B:O,14,FALSE),"")

[nobodyukno]

nobodyukno,

Do you have to make it range specific? Using whole columns on a vlookup should accomplish what you're looking for...
=IF(COUNTIF(OfStats!B:B,U6)>0,VLOOKUP(U6,OfStats!B:O,14,FALSE),"")

It has to be range specific because the lookup value appears in the same column in other parts of the worksheet

[tigeravatar]

In that case this should do the trick:
=IF(COUNTIF(OFStats!$B$51:$B$93,U6)>0,VLOOKUP(U6,OFStats!$B$51:$O$93,14,FALSE),IF(COUNTIF(OFStats!$B$1471:$B$1513,U6)>0,VLOOKUP(U6,OFStats!$B$1471:$O$1513,14,FALSE),""))

[nobodyukno]

In that case this should do the trick:
=IF(COUNTIF(OFStats!$B$51:$B$93,U6)>0,VLOOKUP(U6,OFStats!$B$51:$O$93,14,FALSE),IF(COUNTIF(OFStats!$B$1471:$B$1513,U6)>0,VLOOKUP(U6,OFStats!$B$1471:$O$1513,14,FALSE),""))

Works great! Thanks a lot!