Forum Discussion
Help with INDEX and MATCH
Hello MS Tech community!! I would really appreciate it if you could help me out with this. Please!! Thank you in advance!! How can I write a function in a cell that will search a code in one column, finds it and then match it with another code in a second column in the same table using INDEX and MATCH? Or VLOOKUP?
2 Replies
Let's say that you have a code in cell A2.
You want to search for this code in D2:D1000, and if found, return the corresponding value from F2:F1000.
One way to do this is using INDEX and MATCH:
=INDEX($F$2:$F$1000, MATCH(A2, $D$2:$D$1000, 0))
The 0 as 3rd argument of MATCH specifies that you're looking for an exact match.
This will return #N/A if there is no match. To suppress this error, wrap the formula in IFERROR:
=IFERROR(INDEX($F$2:$F$1000, MATCH(A2, $D$2:$D$1000, 0)), "")
An alternative is VLOOKUP:
=VLOOKUP(A2, $D$2:$F$1000, 3, FALSE)
The 3 indicates that you want to return a value from the 3rd column of $D$2:$F$1000, and the FALSE specifies that you're looking for an exact match.
You can wrap this in IFERROR too:
=IFERROR(VLOOKUP(A2, $D$2:$F$1000, 3, FALSE), "")
If you have Microsoft 365, yet another option is the new XLOOKUP function.
- Thank you very much for your response!
