If you’ve ever needed to compare two columns in Excel — such as employee names or IDs — to check which ones appear in both, you’re not alone. This task is incredibly common in HR and admin work, whether you’re cross-checking payroll lists, training attendance, or active vs. terminated employee records.

Fortunately, Excel provides a simple yet powerful way to do this using the MATCH and ISNUMBER functions. In this post, we’ll walk you through how to compare two columns and identify which values from one list exist in the other — step-by-step.

Use Case

Let’s say you have:

Column A — a list of employees who submitted a form
Column B — a master list of all current employees

Your goal: Check if each name in Column A is present in Column B.

How to Compare Two Columns in Excel Step-by-Step

To explain this concept, I will use the following sample data. It contains two columns of employee names. Some names from List 1 also appear in List 2. Our objective is to identify which employee names from List 1 exist in List 2 and which do not.

Steps:

1. In Column C, add a column header. You can choose any name; in my example, I used ‘Presence in Column B’.
2. In Cell C2 use the Match function by typing the following formula:

=MATCH(A2,B2:B6,0)

How the MATCH Function Works?

The MATCH function in Excel is used to find the position of a specific value in a row or column. It doesn’t return the value itself — it returns the position number where the match is found.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value – The value you want to search for

lookup_array – The range of cells to search in

match_type – Optional. Use:

• 0 for an exact match (most common)
• 1 for less than (for sorted data)
• -1 for greater than (also for sorted data)

In our example, MATCH(A2, B2:B6, 0): Searches for the value in A2 within Column B. If found, returns its position (e.g., 2 in the case of Alice); if not found, returns #N/A. You can autofil rest of the cells to see where is display #N/A

3. Include the ISNUMBER function into the Match function you wrote above by inputting it in the formula bar as shown below:

=ISNUMBER(MATCH(A2,B2:B6,0))

What ISNUMBER Does in the Formula?

The ISNUMBER function in Excel checks whether a given value is a number. It returns:

TRUE if the value is a number

FALSE if the value is not a number, including errors like #N/A, text, or blank cells

Syntax:

=ISNUMBER(value)

value – This is usually the result of another function (like MATCH)

In our example, =ISNUMBER(MATCH(A2,B2:B6,0)) will return True in cell C2 because it has a number 2 (for Alice). You can autofill rest of the cells to see where it returns True or False.

4. Now include ISNumber and Match functions you used earlier into an IF function by typing the following in the formula bar:

=IF(ISNUMBER(MATCH(A2,B2:B6,0)),"Present","Not Present")

This will be the final formula which will produce the desired result and show which names in List 1 are present in List B and which are not present.

How the IF Function Displays the Result

The IF function in Excel allows you to show different results based on whether a condition is TRUE or FALSE.

Syntax:

=IF(logical_test, value_if_true, value_if_false) 

In our example, we use IF to turn the TRUE/FALSE result from ISNUMBER(MATCH(...)) into a user-friendly message i.e. Present or Not Present.

If the name in A2 is found in Column B → MATCH returns a number → ISNUMBER is TRUE → IF returns “Present”

If not found → ISNUMBER is FALSE → IF returns “Not Present”

Final output: Comparing Two Columns in Excel

Function Summary Table

Now that we’ve seen the final result in Column C, let’s break down how each Excel function contributes to this formula. The table below summarizes the role of each function used.

Function	Purpose	Returns	As Used in Our Example
Match	Finds the position of a value in a list	A number (if found) or #N/A (if not found)	MATCH(A2, B2:B6, 0) → returns row number where A2 is found in Column B
ISNUMBER	Checks if a value is a number	True or False	ISNUMBER(MATCH(...)) → returns TRUE if a match exists
IF	Returns a custom result based on whether a condition is true or false	Custom message or result	IF(ISNUMBER(MATCH(…)), "Present", "Not Present")

Comparing two columns in Excel is a common but powerful task — especially in HR, payroll, or training-related work. By combining MATCH, ISNUMBER, and IF, you can quickly identify which values exist in both lists and highlight any missing entries with clear, readable output. This method is one of the easiest ways to compare two columns in Excel.

Ready to Learn More?

Master Excel for real-world HR tasks with our hands-on training!

✅ Learn functions like VLOOKUP, SUMIFS, PivotTables, and more
✅ Build professional reports and HR dashboards
✅ Perfect for HR professionals, recruiters, and team leads

👉 Join Our Excel for HR Course or Explore more Excel blogs

Interested in mastering Excel beyond the basics?

Explore the comprehensive guide Excel Basics to Advanced – Design Robust Spreadsheet Applications Powered with Formatting, Advanced Calculations, Charts, Pivot Tables, and Macros.

---