How to Compare Two Lists in Excel Using VLOOKUP, Comparing two lists in Excel can be a challenging task, especially if you have many items to compare.
One of the easiest ways is to use the VLOOKUP function. If you have two lists with related data, the VLOOKUP function can compare the values in both lists and find the matches.
In this guide, we’ll walk you through the steps of comparing two lists using VLOOKUP.
Step 1: Open the Excel Spreadsheet
The first step is to open the spreadsheet containing the two lists that you want to compare in Excel.
Step 2: Name the Ranges
If your lists are not in a table format, you will need to name the ranges to make them easier to use in the VLOOKUP formula.
To do this, select the range of cells containing the first list. Next, go to the “Formulas” tab and click on “Define Name.”
Data Analytics Online Courses for Beginners » Data Science Tutorials
In the Name Manager dialog box, enter a name for the range in the “Name” field and click “OK.”
Repeat this step for the range containing the second list.
Step 3: Create a Header Row
Create a header row if your lists do not already have one. This makes it easier to reference the columns in the VLOOKUP formula.
Step 4: Insert a New Column
Insert a new column next to the second list that you want to compare with the first. The new column will hold the results of the comparison.
Step 5: Use the VLOOKUP Formula
In the first cell of the new column, type the VLOOKUP formula. The formula has four arguments:
- Lookup_value: This is the value you want to match from the first list. You can select it from the first list directly or use a cell reference.
- Table_array: This is the range of cells where the VLOOKUP function will search for the lookup value. You can enter the name of the range you defined in Step 2 or select the range manually.
- Col_index_num: This is the column number in the table array containing the result you want to retrieve. In this case, it is the column from the second list that you want to compare to the first list.
- Range_lookup: This is an optional argument that specifies whether you want an approximate match or an exact match. Use “FALSE” for an exact match and “TRUE” or “1” for an approximate match.
For example, if your first list is in cells A2:A10, and your second list is in cells C2:D10, with the first column containing the values you are comparing and the second column containing the data you want to retrieve, you can use the following formula:
=VLOOKUP(A2,$C$2:$D$10,2,FALSE)
This formula looks up the value in cell A2 in the range C2:D10 and returns the corresponding value from the second column in that range.
You can copy this formula down to the entire column to compare all the values in both lists.
Step 6: Filter the Results
After copying the formula down the entire column, you’ll have a list of matching values and “#N/A” errors for non-matching values.
You can filter the results to show only the matching values by selecting the new column, going to the “Data” tab, and clicking on “Filter.” In the dropdown menu, uncheck the “#N/A” error and click “OK.”
Step 7: Review the Results
After filtering the results, you can review the list of matching values and use the data to perform further analysis or combine the data from both lists.
Conclusion
Comparing two lists in Excel can be tedious, but using the VLOOKUP function can save you time and effort. By following the steps outlined here, you can use VLOOKUP to match values in two lists and retrieve related data.
Exploratory Data Analysis (EDA) » Overview »
