VLOOKUP lets Excel do the hard work of matching up a value from one spreadsheet to another. It saves time and is an essential Excel tool for cleaning up data and presenting analysis on small datasets.

Step 1: Setting up

Step 2: The Reference Column

Step 3: The Table Array

Step 4: The Index

Step 5: The Match

## Step 1: Setting up

In this example, we have a sheet called Games Report. It has everything we need except the Publisher, which we can find on the Publisher Report sheet.

Both sheets contain the “Game” column which is what we use to match up the two sheets. The same way you would if you were trying to do this manually. This is called the reference column.

## Step 2: The Reference Column

A VLOOKUP works from left to right so the column where the formula, and result, goes needs to be on the right of the reference column. Sometimes this means you may need to rearrange your data.

Type into your formula bar **=VLOOKUP(** and enter the first cell in the reference column.

## Step 3: The Table Array

The second part of the formula is to tell the VLOOKUP where the data we want to return is.

In the example we move to the Publisher Report sheet and highlight all of column A, where the reference data is. And all of column B, where the Publisher data we want to return is.

## Step 4: The Index

The next part of the formula is to tell the VLOOKUP how many columns from the reference column it needs to look for the data we need.

In this example the data we want is in the second column so we need to enter the number 2.

## Step 5: The Match

The last part of the formula is to decide if we want to return an exact match or not. To return an exact match enter a 0 and the formula is complete. If for any reason you do not want an exact match enter a 1.

To tidy things up we just need to copy the formula down and the data will follow.

Remember to copy and ‘paste values’ to ensure you the results remain and not the formula. If for any reason your lookup table moves or is deleted, so will your results.

Excel is easy to learn and allows analysts and stakeholders to speak the same language. While it doesn't replace databases and BI tools for regular reporting, Excel may be just what you need for quick analysis and cleansing of small data sets.

## Read more

This post originally appeared on helenanderson.co.nz

Posted on by:

### Helen Anderson

Making applications go faster at Raygun, AWS Data Hero, and tag moderator on Dev.to.

## Discussion

For some reason I learned

`index`

and`match`

first and vlookup never made sense to me. I always ended up in a case where I wanted something from the right, and the syntax didn't jive with me.`vlookup`

is definitely more of a standard that you need to understand to work with any other Excel users.I learned VLOOKUP first so that's my 'go to'. I find when trying to use INDEX - MATCH that I quickly end up with the spinning wheel of doom as Excel crunches through the function.

In most cases I just end up moving my columns around, and if it's too big for that it's time for a different tool.

How often do you use the

`1`

option?I'm slightly embarassed to admit that I have saved a little reference image for myself that I use every now and then:

In my 15+ years of driving Excel I've never had a reason to look for an inexact match. However ... looks like there are some use-cases:

Using VLOOKUP and inexact match to draw raffle tickets

Using VLOOKUP and The Price is Right

I love excel (more so google spreadsheets) but I always forget the functions.

Every time I think of Sonic on the Sega Genesis I think of the crazy way you could stack carts back in the day.

Thanks for a great article @helenanders26 .

Have you looked at the

`query`

function in Google Sheets? I've found it to be easier to use in many cases and more intuitive than`vlookup`

since it brings SQL SELECT-type of functionality against spreadsheets. Note that this supports queries against other sheets, not databases (e.g. ODBC)The one drawback I've seen is that for large spreadsheets

`vlookup`

performance seems to be much faster.I rarely use Excel, but it may have a similar function as well.

Thanks Jim, that's a good point to bring up. There are plenty of features that (in my opinion) are underutilised. QUERY is one of them, using Gscripts is another.

As with anything in the data world it's up to the analyst which set of tools to use. There needs to be more education around the tradeoffs between them.

This is my favorite Excel function.

It's so satisfying that every time I use it, I feel like a super hero.