CODEWITHSUNDEEP
excelexcel-formula
Peter Nguyen
Peter Nguyen

Reputation: 101

Index Match Multiple criteria

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!

Upvotes: 3

Views: 332

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Ralph
Ralph

Reputation: 9434

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))

Upvotes: 4

Related Questions