How to Match Data in Large Excel Files Using the VLOOKUP Function

There are lots of ways to format data in an Excel spreadsheet. Regardless of how you set up the spreadsheet, matching and locating specific data later is often a necessary tool. Whether you are checking to see if the data was ever entered, verifying the data is correct, or looking for an entirely different reason, knowing the fastest way to find data in a larger Excel file can be helpful.

This post discusses how to use the VLOOKUP function which is especially helpful when trying to locate or match a large set of data in Excel.

How to Match Data in Large Excel Files Using the VLOOKUP Function

One of the most important things to keep in mind when entering data into Excel, regardless of how you think you might use it, is to keep each column, or row, with as specific of data as you can. As an example, if you are capturing names, have a cell for first names and a cell for last names. If you are capturing addresses, have cells for the street number and name, city, state, and zip code.

Generally, the smaller the amount of data in each cell, the easier it will be to locate and match data. If you consolidate data, you run the risk of regretting this decision later. While there are tools for expanding data in one cell to multiple cells, this can be a tedious process and mistakes can be made.

Once you have your Excel spreadsheet, if you are looking for something, you can always use Sorting and Filtering on the Data tab. You can also simply search for what you are looking for when you are only looking for a couple of items. However, if you are trying to match a large data set against another, this is when you want to use the VLOOKUP function.

VLOOKUP takes a set of data , compares it to a range of data you identify, and provides a result in the cell of the function that matches the data column in the range you specify.

Using the VLOOKUP function

• Open the spreadsheet that has the information you want to locate or match data.
• Insert two new columns to the right of the data range you plan to search against by right-clicking on the column you want to insert a column before and selecting "Insert".
• Repeat this process to enter two new columns - one for the data you want to try and match against the existing document and one for the VLOOKUP results.

NOTE: You do not have to place the column after the range, it can be before, but it cannot be in the middle. I prefer the results to be at the end of the range, but you can certainly have it show up before the range.

• Copy the data from your second file into the first newly added column in the original file. Do not worry about matching up the data between columns, VLOOKUP will identify the matches for you.

Type the VLOOKUP function into the second new column, starting with the cell that is next to the first line of data to match. The VLOOKUP function is broken into these parts:

1. First you must start with =VLOOKUP( in the cell.
2. After the open parenthesis, enter the cell ID for the first cell you want to match against the original data range and add a comma after the cell ID.
3. Enter the range of data to look through, seperated by a colon, such as A2:C330 and add a comma at the end. This is one of those places where you should probably use relative cell ranges. To do this, add a $ to the cell row and column. The above range would instead look like $A$2:$C$330.
4. Add the number of the column within the range that you want the results to display if there is a match. This can feel tricky, but if you have a range of B#:F##, a 1 would display the data in column B if there is a match, a 4 would display the data from column 4 if there is a match, etc. Be sure to follow this number by a comma.
5. Lastly, type TRUE if you want a relative match, or FALSE if you want an exact match.
6. Close the function with an ending parenthesis ).

Once you have verified that the function is working properly, copy it down to all the cells next to the data you copied into the column to the left of the function to be sure that all cells are compared. An example of the results looks like this:

VLOOKUP can be a powerful function used to locate and match data between different documents. The beauty of this tool is that after copying some data and typing a single formula, you simply copy the formula to all cells and have instant data matches and results. VLOOKUP helps quickly locate data matches when you have two larger sets of data. With smaller data amounts, you can also use data filters, sorting and even the search tool.

As always, knowing which feature to use for each situation saves time!