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.