Multi-Criteria VLOOKUP

I use SUMPRODUCT to do multi-criteria SUMIF calculations. Is it possible to do a multi-criteria VLOOKUP? I have a table with three columns of text.

I want to enter two codes which match entries in the first two columns. The formula should display the text from the corresponding row in the third column.

By Neale Blackwood

First, if you’re not familiar with the VLOOKUP function, take a look at this step-by-step tutorial.

This article will then make a lot more sense!

Normally, you can only do a VLOOKUP on a single value in a single column.

Here’s a clever way to use two fields and do a VLOOKUP on multiple columns.

It’s a variation on the SUMPRODUCT function.

Row 1 is for headings and the data starts from row 2 to row 10. Columns A and B are the two columns to lookup and column C contains the text to find.

Cell E2 has the value to find in column A and cell F2 has the value to find for column B. In cell G2 the following formula will find the first instance of the combination.

=INDEX(C2:C10,SUMPRODUCT(MATCH(TRUE,(A2:A10&B2:B10)=(E2&F2),0)),1)

The (A2:A10&B2:B10)=(E2&F2) part of the SUMPRODUCT function will return a sequential list of FALSE and TRUE results. A TRUE result means the two criteria have been found on the same row.

The MATCH will return the sequence number of the first TRUE it finds. The INDEX function uses that sequence number to identify the corresponding cell in the range C2:C10.

If the combination is not in the table the formula returns the #N/A error.