Oleinspector February 2016

Use Index Match to Return Value Using Lookup Value from Multiple Columns

I'm trying to return a value from one column using values from a range of 3 columns. When I enter an electrical, plumbing or gas permit number into a report (D14) I want the corresponding building permit number to be entered into another cell as the file reference. The report is one worksheet and the list of permit numbers is another worksheet in the same workbook. It's one permit one report so I don't think I need anything complicated. VLOOKUP didn't work because I need to search right to left.

This is the Index Match formula I've used but I can only use 1 column as the lookup value and I have a column for each type of permit.

=INDEX(Building!$A2:$A25000,MATCH(D14,Building!$D2:$D25000,0))

Answers


Jeeped February 2016

Wrap your MATCH function(s) in IFERROR function(s) so that the lookup is passed to the next column if it is not found.

=INDEX(Building!$A:$A, IFERROR(MATCH(D14, Building!$D:$D, 0),
                       IFERROR(MATCH(D14, Building!$E:$E, 0),
                       IFERROR(MATCH(D14, Building!$F:$F, 0), "nowhere"))))

Post Status

Asked in February 2016
Viewed 3,651 times
Voted 10
Answered 1 times

Search




Leave an answer