Go Back

Check If One Column Value Exists in Another Column

Read time: 20 minutes

When you need to check if one column value exists in another column in Excel, there are several options. One of the most important features in Microsoft Excel is lookup and reference. VLOOKUP, HLOOKUP, INDEX and MATCH functions can make life a lot easier in terms of looking for a match.

In this tutorial, we will see the use of VLOOKUP and INDEX/MATCH to check if one values from one column exist in another column.

In the following example, you will work with automobile parts inventory data set. Column A has the parts available, and column B has all the parts needed. Column A has 115 entries, and column B has 1001 entries. We will discuss a couple of ways to match the entries in column A with the ones in column B. Column C will output “True” if there is a match, “False” if there isn’t.

Check if one column value exists in another column using MATCH

You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column. The syntax for MATCH() is = MATCH(lookup_value, lookup_array, [match_type]). Using MATCH you can look up a value both horizontally and vertically.

To solve the problem in the previous example with MATCH(), you need to follow the following steps:

  1. Select cell C2 by clicking on it.
  2. Insert the formula in"=NOT(ISERROR(MATCH(A2,$B$2:$B$1001,0)))” the formula bar.
  3. Press Enter to assign the formula to C2.

  4. Drag the formula down to the other cells in column C clicking and dragging the little “+” icon on the bottom-right of C2.

Excel will match the entries in column A with the ones in column B. If there is a match, it will return the row number. The NOT() and ISERROR() functions check for an error which would be and column C will show “True” for a match and “False” if it is not a match.

Check if one column value exists in another column using VLOOKUP

VLOOKUP is one of the lookup, and reference functions in Excel and Google Sheets used to find values in a specified range by “row.” It compares them row-wise until it finds a match. In this tutorial, we will look at how to use VLOOKUP on multiple columns with multiple criteria. The syntax for VLOOKUP is =VLOOKUP(value, table_array, col_index,[range_lookup]).

You can check if the values in column A exist in column B using VLOOKUP. 

  1. Select cell C2 by clicking on it.
  2. Insert the formula in“=IF(ISERROR(VLOOKUP(A2,$B$2:$B$1001,1,FALSE)),FALSE,TRUE)” the formula bar.
  3. Press Enter to assign the formula to C2.

  4. Drag the formula down to the other cells in column C clicking and dragging the little “+” icon on the bottom-right of C2.

This will allow Excel to look up all the values in column A and match them with the values of column B. Column C will now show “True” if the corresponding cell in column A has a match in column B, “false” if it does not have a match.

MATCH vs. VLOOKUP

Between MATCH and VLOOKUP, the better option is MATCH. Not only the formula is simpler, but MATCH is also faster than VLOOKUP when it comes to performance.

MATCH uses a dynamic column reference whereas VLOOKUP uses a static one. If you were to add more columns, with VLOOKUP, you would distort the results. But with MATCH you can easily change the reference when inserting or deleting columns.

Another issue with VLOOKUP is the lookup value needs to be on the leftmost column of the array, which is not applicable for MATCH. In the previous example, If we had to check if Column C values exist in column A, VLOOKUP would have provided an #N/A error.

Excel has some very effective functions when it comes to checking if values in one column exist in another column. One of these functions is MATCH. It returns the row number based on the lookup value from an array. Another such function is VLOOKUP. It returns the value based on a lookup value from a static array.

When it comes to which function is faster, MATCH is the winner. For its dynamic reference and easy to change array references, MATCH is preferred to check if values in one column also exist in another column.

Most of the time, the problem you need is probably more complex than just simply checking if a value exists in a column. If you want to save hours of researching and frustration, try our Excel Live Chat service! Our Excel experts are available 24/7 to answer any Excel question you have on the spot. The first question is free.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi, i'm trying to do a vlookup up formula in column N, this looks to match the values in column L with the values in column B and return the value from column E. The data in columns A:F is updated with a macro. The data in J:N is used in a report. I know what the problem is, that the date and time in column B has an extra space between the date and time, hence why the vlookup wont work. But i dont know how to solve this? The data that the macro takes from another worksheet does not have this extra space in it.
Solved by K. D. in 17 mins
I am having trouble with Excel lookup formulas - I need to match two related values (in two separate cells) in one Excel tab, to the same values in another tab, and copy a corresponding name from the second tab into the first one. I can share some of the dataset with you, it is not confidential. The dataset has 2500 records in it, and I would like to find a formula that allows me to return a townland name from one tab in the main dataset, and populate it into the same column in another tab - matched to the same corresponding data in each tab. There may be some examples where the townland name is not visible.
Solved by E. H. in 24 mins
I am trying to use v-lookup to match the time in column q with the time in column a. However, the time scale is not continuous in column a and there are sometimes two time points. for example in a there are two time stamps (10:55:57 PM) with data. The standard v-lookup formula only returns the data for the first time point. Is it possible to get the average? Perhaps using another formula? Also, if there is not a time match for column q and a then I just want the lookup value to be a blank cell.
Solved by G. D. in 24 mins
I have a column of data lets say in P:P, I used the formula =LOOKUP(2,1/(NOT(ISBLANK(P:P))),P:P) to return the last value in this data. However now I need another formula to return another value from the same selection that is not the result of the above formula. Do you know if I can do this via formulas?
Solved by C. J. in 25 mins
Is there a way to have list in 2 columns be sorted out by another 2 column group, and have the closest greater value return as a result? Similar to the VLOOKUP or MIN function. I want to keep the numbers in each group, together. This is for finding the closest aspect ratio to a tv signal. Example: my resolution is 500x320, so the closest resolution out of a list would be 1280x720. I don't want to search each column individually, but rather as a group. Thanks :)
Solved by A. A. in 17 mins
my formula in Excel is supposed to pull the value from another sheet matching the date in row 1, the name in cell $A4 and returning the value What it's doing is pulling the value but not for the correct date. for example value for 4/1/15 is showing in 5/1/15. The issue is on the Totals sheet. This is my formula =IF(VLOOKUP($A$1,'Quick Glance Total'!$A$2:$BO$43,COLUMN()-1,0)="na","#NA",VLOOKUP($A$1,'Quick Glance Total'!$A$2:$BO$43,COLUMN()-1,0)) I can't figure out why it is not working correctly
Solved by D. A. in 23 mins

Leave a Comment

avatar