Excel Compare Two Columns (Table of Contents)
- How to Compare Two Columns in Excel Using VLOOKUP?
- Examples of Compare Two Columns in Excel using VLOOKUP
Compare Two Columns in Excel using VLOOKUP & Find Data
While working with data in Excel, sooner or later, you will be in need to make a comparison between two columns to check whether data from one column is present in another column or not. When it comes to making comparisons between two columns, lookup functions are the best in business. We can use the VLOOKUP function to compare whether two columns have matching data within them or not. This function saves you a huge amount of time while working on a large amount of data where you need to compare two columns. Which increases productivity and reduces the time taken for a task to complete.
How to Compare Two Columns in Excel Using VLOOKUP?
When we have two columns and need to check whether reference values from one column are available in another column, VLOOKUP can be the best alternative to do so. As this function is potent to capture/looks up the data among several other columns based on a lookup value.
Syntax
Arguments
- lookup_value – is the value that is used as reference while looking up
- table_array – is the table/range of the data within which we want to check specific values based on the lookup value.
- col_index_num – is the column number in the range of data from which we are going to get the values after lookup.
- Range_lookup – is an optional argument that specifies whether we need an exact matching value (FALSE) or approximate matching values (TRUE).
We will see some examples using which we can specify how to compare two columns in Excel using the VLOOKUP function.
Examples of Compare Two Columns in Excel using VLOOKUP
we will discuss Examples of Compare Two Columns in Excel.
Example #1
Consider mixed data as shown below in columns A and B of the excel sheet.
In column C, we need to check whether the values in column “Text B” are matching with those in “Text A”. Follow the steps below to get the comparison within these two columns using the VLOOKUP function.
Step 1: Initiate the VLOOKUP function in cell C2 by typing “=VLOOKUP(“.
Step 2: The first argument we need to specify is the lookup value. Since we are trying to lookup whether the values from column Text A are present in a column named Text B, we need to specify the lookup value from column A. Since we are working with cell C2, it is better to use A2 as the lookup value. separate it with a comma (“,”) to specify the next argument.
Step 3: The second argument for the VLOOKUP function is table_array. Which will be the range of data within which we wanted to check the values based on lookup_value. In our case, it is a column named Text B. Thus, select range from B2:B9 as a table_array and fix it using dollar signs so that the range will be the same for the formula when we copy it and paste it across different cells. You can use the keyboard shortcut F4 to fix the table range.
Step 4: Third and most important argument is to specify the column index from the table_array, which can be used to lookup the values. Since we only have one column selected as table_array, we can use the col_index_num value as 1.
Step 5: The last argument is optional, which is range_lookup. Since it specifies whether we want an exact match or an approximate match, we can use TRUE (approximate match) or FALSE (exact match) for the same. We will be interested here in exact matches; thus, we will use FALSE as range_lookup argument.
Note that, we can also use the Boolean values for TRUE and FALSE as 1 and 0, respectively, under the VLOOKUP function.
Step 6: Use closing parentheses to complete the formula and press Enter to get the output. Note that, if the function finds an exact match for the text in column A under column B, it will reflect the text, otherwise it will reflect the #N/A error. Also, drag the formula across rows to get the formula applied for all cells.
This is how we can compare two columns in Excel using the VLOOKUP function. However, the #N/A’s are not looking that great in the data. It may look weird to someone who doesn’t know anything about the formula/function. Let’s see another example where we try to get a more concrete solution for this issue.
Example #2
In this example, we are going to use conditional IF, and ISNA with VLOOKUP to see whether the values from column A are present in column B or not.
Step 1: Start the formula with “=IF(“ and within it, use ISNA as a nested function as shown below:
Step 2: Use the VLOOKUP function to check whether the values from column A are present in column B. same as we used in the first example. Close the parentheses for both VLOOKUP and ISNA functions. See the screenshot below for a better understanding.
Step 3: Now, we need to specify the values for the value_if_true argument for conditional IF. Specify it as “Not Present”. Don’t forget to use double quotes.
Step 4: For value_if_false, we need to specify the value. Use “Present” as a value for the argument. See the screenshot as shown below:
Step 5: Close the parentheses to complete the formula and press Enter button to get the output in cell C2. Also, drag the formula across the rows to get the desired output in terms of “Present” or “Not Present”. See the screenshot below for your reference.
How does this work? Well, it is simple. Since ISNA checks whether the VLOOKUP returns #N/A or not, if there are any #N/A, the function returns TRUE, and thus IF function has “Not Present” as an argument for this output. Similarly, if ISNA doesn’t find #N/A, then the value it returns is FALSE, and we have “Present” as an argument under the conditional IF statement.
Thus, wherever we are getting Not Present, those are the values from column A which don’t have matching in column B.
We will wrap this article to end it here. However, we have some points to remember for you all.
Things to Remember
- VLOOKUP doesn’t need the values to be ordered within the two columns while comparing the same.
- If VLOOKUP doesn’t get the match for lookup_value under table_array, it returns by default as #N/A error.
- We can combine VLOOKUP with IF and ISNA to generate a more versatile result where no #N/A gets reflected within the data for those values which doesn’t match.
Recommended Articles
This has been a guide to Compare Two Columns in Excel using VLOOKUP. Here we discuss How to Compare Two Columns in Excel using VLOOKUP, along with practical examples. You can also go through our other suggested articles –
