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.
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query