loading...
Cover image for How to use the VLOOKUP Excel function

How to use the VLOOKUP Excel function

helenanders26 profile image Helen Anderson Updated on ・3 min read

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.

Alt Text


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.

Alt Text


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.

Alt Text


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.

Alt Text


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.

Alt Text


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:

helenanders26 profile

Helen Anderson

@helenanders26

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

Discussion

markdown guide
 

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.

 

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.

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:
reference search
reference itself

 

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.