excelexcel-formulaexcel-match

Using Match to return value from one of 9 columns

This formula works for a single column:
=IFERROR(MATCH([@[TerminalID]],tblMaster[LOCATION_ID 1],0),"No ID Match")

This works unless the matched value is in tblMaster[LOCATION_ID 2] through *ID 9]

I tried the following answer (Use Index Match to Return Value Using Lookup Value from Multiple Columns) but it is very bulky for 9 columns.

Can I make the MATCHfunction search multiple columns?

For what it is worth, I have also tried the following:
=IFERROR(MATCH([@[Terminal ID]],tblMaster[[LOCATION_ID 1]:[LOCATION_ID 9]],0),"No ID Match")
and
=IFERROR(MATCH([@[Terminal ID]],'Master List'!O:W,0),"No ID Match")

Solution

  • Use AGGREGATE:

    =IFERROR(AGGREGATE(15,7,ROW(tblMaster[[LOCATION_ID 1]:[LOCATION_ID 9]])/([@[Terminal ID]]=tblMaster[[LOCATION_ID 1]:[LOCATION_ID 9]]),1),"No ID Match")