CODEWITHSUNDEEP
excelexcel-formula
Royce
Royce

Reputation: 1595

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

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

Upvotes: 0

Views: 583

Answers (1)

Harun24hr
Harun24hr

Reputation: 37155

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)

enter image description here

Upvotes: 1

Related Questions