Vlookup formula to compare two columns in different sheets

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

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 in present in another column or not. When it comes to make comparison 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 which 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 lookup value. Since we are trying to lookup whether the values from column Text A are present in column named Text B, we need to specify lookup value from column A. Since we are working with cell C2, it is better to use A2 as lookup value. separate it with comma (“,”) to specify the next argument.

Step 3: Second argument for 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 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 formula when we copy it and paste across different cells. You can use 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 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 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 VLOOKUP function.

Step 6: Use closing parentheses to complete the formula and press Enter to get the output. Note that, if function finds an exact match for the text in column A under column B, it will reflect the text, otherwise it will reflect #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 VLOOKUP function. However, the #N/A’s are not looking that great in data. It may look weird for 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, ISNA along with VLOOKUP to see whether the values from column A are present in column B or not.

Step 1: Start formula with “=IF(“ and within it use ISNA as a nested function as shown below:

Step 2: Use 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 function. See the screenshot below for a better understanding.

Step 3: Now, we need to specify the values for 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 this works? 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 as FALSE and we have “Present” as an argument under conditional IF statement.

Thus, wherever we are getting Not Present, those are the values from column A which doesn’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 About Compare Two Columns in Excel using VLOOKUP

• 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 have 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 –

1. Date Formula in Excel
2. HLOOKUP Formula in Excel
3. Excel RATE Formula
4. Interactive Chart in Excel