INDEX and MATCH : value not on the first column, formula returning #N/A

Question

I have this kind of Excel file:

A    | B    | C          | D    | E    | ... | Z
S1   | INT  | 25/06/2019 | Tom  | 1002 | ... | myFormula
S1   | CLO  | 26/08/2020 | Tho  | 1002 | ... | myFormula
S1   | UNA  | 13/11/2019 | Nath | 1003 | ... | myFormula
S1   | REJ  | 14/04/2021 | Lee  | 1005 | ... | myFormula 
S1   | INT  | 17/08/2018 | Joh  | 1005 | ... | myFormula

I would like to get content of column C if B and E are matching specific value. So I'm trying to use =INDEX(C2:C65000;MATCH(E2;E2:E65000;FALSE);MATCH("INT";B2:B65000;FALSE)) to get 25/06/2019 in Z2.

Or =INDEX(C2:C65000;MATCH(E6;E2:E65000;FALSE);MATCH("INT";B2:B65000;FALSE)) to get 17/08/2018 in Z6.

However, formula is returning #N/A.

Did you already faced this behavior ?

Thank you

Regards

Your second match argument will index the column number, while what you're trying to get is a match for both arguments. This can be accomplished by an array formula, however you're referencing the matching cell to it's own range, so the result will always be the row you're searching in (if Excel doesn't error). What is the purpose of your formula? — P.b
– P.b, Commented Apr 29, 2021 at 7:47
Thank you for your comment. I would like to get the date in the column C if the column B contains INT. At the end, I need to do calculation based on this date. So, this is just a intermediary step. — Royce
– Royce, Commented Apr 29, 2021 at 7:51

Harun24hr · Accepted Answer · 2021-04-29 07:54:23Z

1

Try INDEX/MATCH like-

=INDEX($C$2:$C$6,MATCH(E2&"INT",$E$2:$E$6&$B$2:$B$6,0))

You may need to array entry CTRL+SHIFT+ENTER for non O365 version of excel.

If you have Excel365 then can try XLOOKUP()

=XLOOKUP(1,($E$2:$E$6=E2)*($B$2:$B$6="INT"),$C$2:$C$6)

answered Apr 29, 2021 at 7:54

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

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

2 Comments

Royce Over a year ago

However XLOOKUP is very slow (my file has more than 20000 rows) ... Any idea why ?

P.b Over a year ago

Cool, I didn't know you could combine the search values and ranges with index-match. I always use match(1,(condtion1)*(condition2),0). Nice to learn different approaches.

Collectives™ on Stack Overflow

INDEX and MATCH : value not on the first column, formula returning #N/A

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related