VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)

Trying to Use VLOOKUP

In a normal VLOOKUP, the syntax is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The lookup_value is the data you are searching with.

The table_array defines the table that you want to look through. The first column must be the column that has the lookup_value in it.

The col_index_num is the number of the column in the table_array that has the data you want to find.

The optional range_lookup specifies whether the list is sorted or not. (TRUE means that VLOOKUP stops looking when it finds something that comes later in the alphabet than the lookup_value. FALSE means it searches the entire list.)

If we were looking for just the Model of car using VLOOKUP in our example data, it would look like this:



A2 holds the Model of car we want to find.

C4:H1159 is the table we want to search through. Column C is the column with the Model information.

Column 6 is the column that holds the Combined Fuel Economy figure that we want to find.

The result of the VLOOKUP is this:


It finds the first entry that matches – the 1.5 liter engine with 38 MPG. This is a problem if, for example, you want to find the fuel economy of the 2.4 liter sport version. To do that, we need to use INDEX and MATCH.