Index Match Multiple criteria

Question

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!

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
– user4039065, Commented Mar 17, 2017 at 16:50

Ralph · Accepted Answer · 2017-03-17 16:39:48Z

4

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

edited Mar 17, 2017 at 16:39

answered Mar 17, 2017 at 16:36

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Nguyen Over a year ago

Thank you! I didn't know you can use IFERROR this way. I usually only use it to get rid of NULL.

Community · Accepted Answer · 2017-05-23 10:30:50Z

2

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.

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Mar 17, 2017 at 16:43

user4039065

4 Comments

user4039065 Over a year ago

Thanks @Ralph - still getting used to a Surface's bat-crap-crazy keyboard.

Peter Nguyen Over a year ago

Thanks! this works as well! Out of curiosity if I were to expand this to have it look for a second 7443 occurrence how would I implement that into my formula?

user4039065 Over a year ago

Well, that would be a new question since you made no mention of multiple returns but you could find an answer here. That method could also be used to return multiple matches for 7443 OR 7664.

Peter Nguyen Over a year ago

Pefect I can definitely use that! Thanks!

Collectives™ on Stack Overflow

Index Match Multiple criteria

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related