To check which values from one column appear in another, use VLOOKUP pointing at the second column and returning column 1: =VLOOKUP(A2,$D$2:$D$11,1,0). It returns the name itself when there’s a match and #N/A when there isn’t. Lock the lookup range with dollar signs before dragging down, or the range slides and rows drop out of it.
For a readable result, wrap it in IFNA rather than IF+ISERROR: =IFNA(VLOOKUP(A2,$D$2:$D$11,1,0),"Not in list") — IFNA only traps genuine misses, so a broken range still shows as an error instead of being labelled “Not in list”. Two simpler alternatives are usually better here.
=IF(COUNTIF($D$2:$D$11,A2)>0,"Match","No match") does the same job without any error handling at all, since COUNTIF returns 0 rather than an error. And to see the differences at a glance rather than in a helper column, select both columns and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
VLOOKUP is the easiest way to match two columns with each other and get a result showing which values match. In the below example, we have two lists of names. In the first, we have 20 names, and in the second, we have 10 names.

Now you need to compare both columns with each other using VLOOKUP.
Comparing the First Column with the Second Column
You need to follow the below steps:
- In column B, next to column A, enter VLOOKUP in cell B2.
- In the lookup_value, refer to cell A2.
- For table_array, refer to the range D1:D11; to freeze the range, you can use dollar signs ($D$1:$D$11).
- Type 1 in the col_index_num.
- And in the range_lookup, enter 0 (exact match) and close the function.

Now, hit Enter to get the result and then drag the formula down to the last name. As you can see, four values in the column match the names in the second column.
=VLOOKUP(A2,$D$1:$D$11,1,0)

And if you want to compare the second column with the first column, you can use the same formula.

=VLOOKUP(C2,$A$1:$A$21,1,0)
You need to freeze the range by using the dollar sign ($) in the table_array so that when you drag the formula down, it won’t move the table_array along with it.
Getting a Meaningful Answer to the Result
As you can see, we have an #N/A error for the values not in the second column. Now to convert this error to something meaningful, you can use the below formula:
=IF(ISERROR(VLOOKUP(A2,$D$1:$D$11,1,0)),"Not Available","Available")

This formula uses ISERROR to check the error and then uses IF. If there’s an error, it returns a value you have specified; otherwise, another value you have set.
In Excel 2013 and later, you can also use IFNA, which only catches the #N/A error, so other errors (like a wrong range) still show up:
=IFNA(VLOOKUP(A2,$D$1:$D$11,1,0),"Not Available")
Compare Two Columns with COUNTIF
You can get the same result without VLOOKUP. COUNTIF counts how many times the name appears in the second list, and if it’s more than 0, the name is there:
=IF(COUNTIF($D$2:$D$11,A2)>0,"Available","Not Available")
Another short option is MATCH with ISNUMBER, which returns TRUE when the name is in the second list and FALSE when it isn’t:
=ISNUMBER(MATCH(A2,$D$2:$D$11,0))
Highlight Matching Names with Conditional Formatting
If you want to see the matches without a helper column, select A2:A21, go to Home ➜ Conditional Formatting ➜ New Rule ➜ Use a formula to determine which cells to format, enter the following formula, and pick a fill color:
=COUNTIF($D$2:$D$11,A2)>0
Use =COUNTIF($D$2:$D$11,A2)=0 instead to highlight the names that are missing from the second list.
Why Some Names Don’t Match
- Extra spaces: “Mary ” with a space at the end doesn’t match “Mary”. Use TRIM on the lookup value, like VLOOKUP(TRIM(A2),$D$1:$D$11,1,0), or clean the data first.
- Upper and lower case: VLOOKUP isn’t case-sensitive, so “JOHN” and “john” count as a match. If the case matters, use EXACT (formula below this list).
- Numbers stored as text: an ID stored as text doesn’t match the same ID stored as a number.
=SUMPRODUCT(--EXACT(A2,$D$2:$D$11))>0
Frequently Asked Questions
How do I compare two columns and return a value from a third column?
Make the range wider and change col_index_num. For example, if D2:E11 has names and amounts, this returns the amount for the name in A2:
=VLOOKUP(A2,$D$2:$E$11,2,0)
Can I use XLOOKUP to compare two columns?
Yes, in Excel 2021 and Microsoft 365. It also lets you set the not-found message without IFERROR:
=XLOOKUP(A2,$D$2:$D$11,$D$2:$D$11,"Not Available")
Why does my formula show #N/A for names that are in the list?
Usually, there’s an extra space or a different character in one of the names. Check the length of both cells with LEN, and use TRIM to remove the extra spaces.
One easy-to-miss distinction in the VLOOKUP example is the lookup direction. A match for every entry in column A proves that A is contained in B; it does not prove the lists are equal. Adding one extra code at the bottom of B makes a handy test. Swap the ranges for a second pass to catch it.