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.

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.

The games report sheet

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.

Excel vllokup
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.

Excel Vlookup
The table array

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.

The index number

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 the results remain and not the formula. If for any reason your lookup table moves or is deleted, so will your results.

Excel Vlookup
The match

Photo by Jesús Mirón García from Pexels