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.
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.
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.
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.
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.
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.
This post originally appeared on helenanderson.co.nz