The Easiest Way to Match VLOOKUP Results Back to Your Original Data File

VLOOKUP is a function you can use in Microsoft Excel to compare two sets of data. This allows you to locate duplicates and/or unmatched data depending upon what data you have and what you are looking for. One of the nice things about using VLOOKUP is it compares data across several rows or columns and can display the results of your choice.

For more about how and when to use VLOOKUP, check out our last post How to Match Data in Large Excel Files Using the VLOOKUP Function.

This post discusses the easiest way to match VLOOKUP results back to your original data file.

The Easiest Way to Match VLOOKUP Results Back to Your Original Data File

When you have really large data sets, meaning the data spans across 20+ columns and thousands of rows, using VLOOKUP might seem less helpful. The reason why is you don't want to have to merge large data sets like this. To do so would require saving a bunch of different documents to preserve your original data and/or a bunch of preplanning to be sure you don't mix up the data.

However, VLOOKUP can be just as useful when comparing two very large data sets as there is an easy way to match the VLOOKUP results from one document and identify the matches in your original document.

Once you have the VLOOKUP results in one document, filter to sort for only those results so that only the matched items are showing in the column.

To add a filter:

Click on the 1 for row one to select the entire row.
Click on the Data tab at the top of the ribbon menu and click the Filter button.

Click on the Data tab in the ribbon menu, then click on the Filter button to add filters.

On the drop-down for the column where you entered the VLOOKUP formula, uncheck the boxes next to "#N/A" for non-matches and "Blanks".

In the filter menu, click to uncheck the boxes next to the non-matches (#N/A) and blanks.

To match the results of your VLOOKUP in the original document:

With the list filtered, copy the original unique set of data that you used for the VLOOKUP function.
In my original post about using the VLOOKUP function referenced at the beginning of this post, I compared service items. I will copy this same list next to the matches now that my data is filtered to matches only.

Copy all of the data you originally copied from one sheet into the other and used in the VLOOKUP formula.

Next, go back to the document where you copied the information from that was pasted into the secondary document now containing the VLOOKUP formula.
When you paste this data, you want to locate the column where the data was copied from, scroll down past all of the entries, and paste the data into the first open cell in that same column.

Locate the column the data was copied out of, scroll down past the last entry and copy your matched data into the same column beginning in the first open cell.

Click on the cell above the column of the data you copied back to select the entire column.
On the Home tab in the ribbon menu, click on "Conditional Formatting".
In the pop-out menu, select "Highlight Cells Rules", then click on "Duplicate Values".

Click on Conditional Formatting, then select "Highlight Cells Rules", then "Duplicate Values" to locate the duplicates.

If desired, use the drop down to select the color that will be applied to duplicate entries.
Click "OK" to apply the rule.

Click "OK" to apply the rule after selecting the colors for located duplicates if desired.

When the results come back, some items will be highlighted in the color that matches the duplicates rule you just created. With these results, if you only need the matched data, you can filter and remove all items that did not have matches.

Filter the data in the column where you applied the duplicates rule.
Click on the filter for that column and select "Filter by Color", then select "No Fill".

Click to filter the column where the duplicates were located and select "No Fill".

Remove all of the cells that were not duplicates by highlighting the results of your filter, then right-clicking on the rows and selecting "Delete Row".

Select all rows that were not duplicates, highlight the rows and then right-click and select "Delete Row".

From here, go back to your filter and click "(Select All)".
To fully clean up your data, remove the data in the column that you copied back where there is no other data in the sheet.

Remove the data you copied at the bottom of the sheet that identified your duplicates.

Once this has been done, you have identified only the data that was matched between two large Excel files using the VLOOKUP function and then easily matching these back to their original records in the original file.

VLOOKUP is a very powerful function in Excel. When you are working with large files, it can seem like it would take forever to locate matches. However, if you run the VLOOKUP function and find matched data, you simply need to copy those matches back to your original file and locate the duplicates. Once this is done, you can remove any non-duplicates and have only your matches remain if you like.

As always, knowing how to use embedded functions as well as how to easily locate and manipulate large sets of data saves a lot of time!