VLOOKUP is a flexible and versatile function. However, using a combination of INDEX MATCH functions are a far superior technique to extracting data from a cell or range of cells.
Major Differences Between INDEX MATCH & VLOOKUP
| INDEX MATCH | VLOOKUP |
|---|---|
| Locate cells within a data set. Dynamic column reference. | Can only locate a cell in one column. Static column reference. |
| Can produce entire column or rows of data. As well, as data from a single cell. | Only produce data from one cell. |
| Match cells outside of your indexed range. | Must search within first column of range. |
| Extract data from in rows above and below and or in columns before and after the match. Use simple addition or subtraction operations to offset extracted data. | Only extract data from columns to the right of the search value. Can’t use the OFFSET function with a VLOOKUP function. |
| Additional columns will not break your formula. | Additional columns will require updating your VLOOKUP formulas. |
Copy our practice sheet as we discuss and display each of these differences to better showcase why INDEX MATCH is a formula you will leverage again and again. INDEX MATCH allows you to create more advanced spreadsheets.
Dynamic Column Reference
INDEX MATCH allows for dynamic column references. In our practice sheet in the “Static Column Example” you will find formulas to locate the gas price dependent upon the select state and gas type. We can extract the correct data using both INDEX MATCH and VLOOKUP.
=INDEX('Gas Prices'!B:F,MATCH(A2,'Gas Prices'!B:B,0),MATCH(B2,'Gas Prices'!B1:F1,0))INDEX MATCH formula
=VLOOKUP(A2,'Gas Prices'!B:F,MATCH(B2,'Gas Prices'!B1:F1,0),FALSE)VLOOKUP formula
Formulas Explained
In the INDEX MATCH formula we are indexing columns B:F in our “Gas Prices” tab, the INDEX function then expects a row and a column numerical digit. We use match to produce the correct row and column for the corresponding cell. Our row MATCH function is looking for an exact state match in the state column where our gas price data is stored. The column MATCH function is looking for an exact match for the gas type across the top of our gas price data. This will result in extracting the correct data.
For the VLOOKUP function we have set the search_key to find the selected state in A2 in the range of columns B:F in our “Gas Prices” tab. Typically, the index value is a numerical digit and is part of the reason for columns not being dynamic. But to keep the comparison fair we will utilize the MATCH function again to produce the correct column that corresponds to the selected gas type. The final variable of FALSE is to indicate that the column is not sorted, in which case the closest match for search_key would be returned.
Both of these functions work and produce the same correct value for the selected variables.
Formula Differences
VLOOKUP functions require the first column in the range will be the column that holds the value referenced in the search_key. INDEX MATCH formulas don’t have this requirement. If we were to expand our formulas to include column A which holds the region of each state, INDEX MATCH formulas will continue to work, while a VLOOKUP function will produce an error.
=INDEX('Gas Prices'!A:F,MATCH(A2,'Gas Prices'!B:B,0),MATCH(B2,'Gas Prices'!A1:F1,0)Expanding the indexable range will still produce the correct value.
=VLOOKUP(A2,'Gas Prices'!A:F,MATCH(B2,'Gas Prices'!B1:F1,0),FALSE)Expanding the range will produce a #N/A error, because the state can’t be found in the first column.
Also, consider if we didn’t use a MATCH function in the VLOOKUP formula than we would also need to adjust the index to the correct column anytime we add columns.
Extract Entire Columns or Rows
INDEX MATCH formulas have a unique advantage, you can extract and entire row or column that match your parameters. VLOOKUP functions can only extract data from an individual cell. In our practice sheet under “Extract Row Example” tab you can see a practical example of this application.
We want to extract gas prices for an entire state that is not dependent upon gas type. Review the code below to see how you could accomplish this.
=INDEX('Gas Prices'!C:F,MATCH(A2,'Gas Prices'!B:B,0),)This is the only formula needed to accomplish our goal.
=VLOOKUP($A$2,'Gas Prices'!$B:$F,MATCH(B$1,'Gas Prices'!$B$1:$F$1,0),FALSE)This formula must be copied to the corresponding cells. That is why we must also add absolute cell references.
Formula Explained
One of the best uses of INDEX MATCH formulas is extracting data across an entire row or column within the indexed range. Note, in the INDEX MATCH formula above we have left the [column] reference blank. This will result in only finding the correct row that matches our desired state and then return every value in our index for that row. This feature can be easily leveraged to create dynamic dropdown menus.
The VLOOKUP function can only produce the contents of a single cell. To produce all 4 of the different gas types by state we will need to write 4 VLOOKUP formulas. This will also mean you will need to start using absolute cell references to utilize the autofill functionality.
Extract Cells Outside Your MATCH
You’ll notice in the 2 formulas posted above the INDEX MATCH is indexing 'Gas Prices'!C:F but the MATCH function to return the numerical row value for the state gas prices is being located in column B. VLOOKUP functions can only extract values that are set within their range and index.
This is a great feature when you are creating dynamic dropdown menus through a data validation criteria set to “List from a range” as it can make ignoring headers and extracting data within a row or column very simple.
Extract Data From Any Indexed Position
If you need to extract data in a different row or column than the returned value you can do so easily with an INDEX MATCH formula.
In our practice sheet there is an “Extraction Example” tab. For this example we would like to return the correct region for the selected state. VLOOKUP shows it’s limitations as the function can only return data to the right of the search column.
=VLOOKUP(A2,'Gas Prices'!B:B,-1,FALSE)If we try to put a negative number as an index we will receive a #VALUE!error.
Function VLOOKUP parameter 3 value is -1. It should be greater than or equal to 1.
=OFFSET(VLOOKUP(A2,'Gas Prices'!B:B,1,FALSE),,-1)If we try to use the OFFSET function to return a cell out of range we receive a #NA error.
Argument must be a range.
There is no simple way to extract data from a cell to the left of our VLOOKUP function.
However, with an INDEX MATCH formula we can easily search for the selected state in the dropdown and extract the contents from the appropriate column. Keep in mind you will need to have indexed the column you want to extract data from, you cannot extract data not contained within your index.
=INDEX('Gas Prices'!A:F,MATCH(A2,'Gas Prices'!B:B,0),1,)This is useful because we don’t need to readjust our data just to make a VLOOKUP function work properly. With INDEX MATCH we are able to quickly extract data from columns before or after our MATCH functions or rows above and below our MATCH functions.
Additional Columns
With an INDEX MATCH formula you can easily add columns to your data set because your formula is searching for a match across all indexed cells. VLOOKUP functions require a numerical value for the index in the function. This means if you add another column the VLOOKUP formulas will not update the index and you will have to adjust each formula.