Working with large sets of data can often be intimidating. There may be times when you need to identify one column of data compared to another, either to quickly identify which values are missing, or to pull out specific cells. So how do you do that without manually reviewing each cell? The answer – Vlookup.
You need to know how to use Vlookup in Excel. If you’re unfamiliar with Vlookup, this short guide will give you some quick tips on how to Vlookup another column in an Excel Worksheet.
Table of Contents
Why Use Vlookup to Compare Columns?
There are a few common reasons to use Vlookup to compare columns – we will focus on using Vlookup to identify missing or different values between two columns. As we can see in our example, we have two lists of states. We can use Vlookup to quickly identify which states are not on both lists. You can follow this using learning materials.
The Vlookup formula
Rather than manually reviewing each list, we can use the Vlookup formula to flag states that do not appear on both lists. In cell C4, let’s start entering the Vlookup formula. To identify states that do not appear on both lists, we will use the following formula:
=VLOOKUP(A2,B:B,1,FALSE)
This formula is pointing to states in List 1, checking if they are in Column B, and identifying any states that are in both lists in Column C.
To break this down further, A2 is pointing to Alabama in State List 1. The formula is identifying Alabama to search in our range of data we identify, which is Column B (B:B). Since we are only searching in one column, we simply put 1 as the column index number. Finally, we need to include FALSE as the range_lookup value so it only shows exact matches when comparing the lists.
When you enter the Vlookup formula, any state names that appear in the output column (Column C) are present in both lists. Any cells that show #N/A mean that the state in the same row in Column A is not present in Column.
By using the Vlookup function to look up data in a different column, you can easily find and return data from a table, even if it’s not in the first column. With just a few changes to the column index number argument, you can look up data from any column in the table.
