Index Match Multiple criteria

3
votes

Currently I am using this formula:

=INDEX(STP_Data!F70:G78, MATCH(7443, STP_Data!$G$70:$G$78, 0), 1)

I need to have it also look for 7664 if it does not find 7443. I don't know how to approach including this into the formula. Do you guys have any idea? Thanks!

2
excelexcel-formula
Your MATCH locks the lookup range with absolute columns and rows but the return range in INDEX does not. Shouldn't both be locked or both be unlocked? – user4039065

2 Answers

4
votes

Give this a try and let me know if that solves it:

=IFERROR(INDEX(STP_Data!F70:G78, MATCH(7443, STP_Data!$G$70:$G$78, 0), 1), INDEX(STP_Data!F70:G78, MATCH(7664, STP_Data!$G$70:$G$78, 0), 1))
2
votes

The IFERROR function can pass processing to an alternate if the first attempt fails. Like Ralph's solution above but uses IFERROR internally on the MATCH for a slightly shorter formula.

=INDEX(STP_Data!F70:F78, IFERROR(MATCH(7443, STP_Data!$G$70:$G$78, 0), MATCH(7664, STP_Data!$G$70:$G$78, 0)))

You don't need to specify the column in INDEX if you only reference column F.