Match Two Lists With The MATCH Function

Key Takeaways

• The MATCH function in Excel can be utilized to determine if a value from one list is present in another, effectively allowing for the comparison of two lists for matching entries.
• If the MATCH function finds a match, it returns the row number of the corresponding item in the second list; when no match is found, it displays #N/A.
• The process can be streamlined by applying the MATCH formula to multiple cells, enabling a comprehensive comparison of the lists in a single action.

Don’t forget to download this Excel Workbook to follow along and compare two lists in Excel for matches:

Highlight Row Difference

You can easily highlight differences in value in each row using the conditional formatting feature in Excel. It will provide you with an idea of how many lines in the columns differ in values.

In the data below, you have two lists in Column A and Column B respectively.

Follow the steps below to highlight row difference:

STEP 1: Select both the columns.

STEP 2: Go to Home > Find & Select > Go To Special or simply press keys Ctrl + G and Select Special to open the Go To Special dialog box.

STEP 3: Select Row Difference and Click OK.

And, Voila!

All the values in Stock List 2 that do not match with the corresponding value in Stock List 1 have been highlighted.

 

STEP 4: You can mark these cells with color as well. Go to Home > Font Color > Select Red.

This will permanently highlight the cells in red font color for future reference.

 

Compare Row using IF function

You can use the IF Function to compare two lists in Excel for matches in the same row. If Function will return the value TRUE if the values match and FALSE if they don’t.

You can even add custom text to display the word “Match” when a criterion is met and “Not a Match” when it’s not met.

Let’s see how we can compare two lists in Excel for matches using IF Function:

STEP 1: We need to enter the IF function in a blank cell.
=IF(

STEP 2: Enter the first argument for the IF function – Logical_Test

What is your condition?

The value in cell D12 is equal to the value in cell C12.
=IF(D12=C12,

 

STEP 3: Enter the second argument for the IF function – Value_if_true

What value should be displayed if the condition is true?

The text displayed should be Match if D12 is equal to C12.
=IF(D12=C12,"Match",

 

STEP 4: Enter the third argument for the IF function – Value_if_false

What value should be displayed if the condition is false?

The text displayed should be Not a Match if D12 is not equal to C12.
=IF(D12=C12,"Match","Not a Match'')

 

STEP 5: Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

 

Compare List using Match Function

Before we understand how to compare two lists in Excel for matches, let’s first go through the basics of what the MATCH function Excel does.

 

What does it do?

It returns the position of an item in a range.

Formula breakdown:

=MATCH(lookup_value, lookup_array, [match_type])

What it means:

=MATCH(lookup this value, from this list or range of cells, return me the Exact Match).

---

I am sure that you have come across many occasions where you have two lists of data and want to know if a specific item in List1 exists in List2.

Well, I have!

With the MATCH function, you can verify if a cell´s item in List1 exists in List2.

The function will return the row position of that item in List2 hence confirming that it exists.  If you get a #N/A it means that the cell´s item does not exist in List2.

You can then go ahead and filter your List1 with either the values returned or the #N/As.

Here are our 2 Lists:

STEP 1: We need to enter the MATCH function in a blank cell:
=MATCH(

 

STEP 2: Enter the first argument for the MATCH function – Lookup_value

What is the value you want to check?

Select the cell containing the List1 value, as this is what we want to check against List2.
=MATCH(C12,

 

STEP 3: Enter the second argument for the MATCH function – Lookup_array

What is the list you want to check against?

Select the entire List2.

And ensure to press F4 to make it an absolute reference.
=MATCH(C12, list2!$C$12:$C:21,

 

STEP 4: Enter the third argument for the MATCH function – Match_type

How specific is your matching? We want an exact match so place in 0.
=MATCH(C12, list2!$C$12:$C:21, 0)

 

STEP 5: Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

 

You now have all of the results!

You can see which row numbers the items exist in List2. For example, Mon45657 in List1 exists in List2 Row 9! If it does not exist in List2, then #N/A is displayed.

 

Using either of the three ways mentioned in this article, you can easily compare two lists in Excel for matches!

Practical Scenarios for List Comparison

Exact Row Matches with MATCH Function

When you’ve got lists in Excel, determining where exact values line up is like playing detective. The MATCH function is your magnifying glass. Imagine wanting to pinpoint a value’s position within a column. You’d set MATCH on the case, and, voila, it tells you exactly which row the suspect – ahem, your value – resides in. A common use case? Linking data across different sheets.

Say you’ve got a list of employees in one column and their unique IDs in another. To find in which row “Jamie” is mentioned, use MATCH to get their row number. With this positional number handy, you can use other Excel functions to retrieve additional data associated with Jamie.

Remember to place 0 as the third argument for an exact match. This small but crucial detail ensures you get an accurate result, pointing you to the exact row number you’re after like a well-trained sleuth.

Here’s what your MATCH might look like: =MATCH("Jamie", A2:A100, 0)

Identifying Mismatches Between Lists

When you’re juggling two sets of data, ensuring they’re in harmony is a top priority. Luckily, Excel’s MATCH function helps you in catching the odd ones out. Identifying mismatches between lists means finding what’s in one list that’s not in the other, a bit like playing ‘spot the difference’.

You might be auditing inventory or making sure your email list hasn’t missed any subscribers. By using MATCH alongside an error-catching function like ISNA, you’re equipped to flag discrepancies. It’s a double act where MATCH scouts for the value, and ISNA signals if it’s not found.

Here’s an example for you to try: =ISNA(MATCH(value from List A, Range of List B, 0))

This formula will return TRUE when a value from List A is missing in List B. That’s your cue that there’s a mismatch, prompting further investigation or correction.

Tips and Tricks for Optimizing Your MATCH Formulas

Handling Case-Sensitivity Issues in List Comparison

While Excel’s MATCH function is a powerhouse, one must remember it doesn’t discriminate between uppercase and lowercase letters, which might be an issue in certain data sets. Need to treat “Data” and “data” as unique entries? Then it’s time for a workaround to make your list comparison case-sensitive.

Transform MATCH into a detail-oriented tool by partnering it with the EXACT function, which strictly compares text, taking the case of each letter into account. The combo goes something like this: =MATCH(TRUE, EXACT(Cell Range, "Your Text"), 0)

Now, if “Your Text” doesn’t match the exact case in the cell range, MATCH won’t find it, preserving the sanctity of your case-sensitive data. Just remember, this can be an array formula, so press Ctrl+Shift+Enter if you’re not using Excel 365 or later.

Avoiding Common Errors with MATCH Function

Dive into using MATCH and, just like any deep sea exploration, you might encounter some unexpected challenges. The most common error you’ll encounter is the dreaded #N/A, Excel’s SOS signal telling you it can’t find what you’re looking for. But fret not; with some savvy tips, you’ll be navigating smoothly in no time.

Firstly, check your lookup value. Is it spelled correctly? Are there any extra spaces? Excel is particular about details. A second glance could save you lots of head-scratching.

Next up, scrutinize the lookup array. The range should be a single column or row, not a mishmash of both. And remember to lock your array with absolute cell references (those dollar signs in $A$1:$A$100) if your formula needs to stay constant across multiple cells.

Lastly, ensure the match type reflects your intent. Want an exact match? Zero is your hero. Leave it set at 0 to avoid unintentional wild goose chases with approximate matches.

If #N/A keeps popping up and you’re positive everything’s in order, it might just be that the value truly isn’t there. Time to play detective again and figure out why.

FAQ: Frequently Asked Questions

How Do I Use MATCH to Compare Two Columns in Excel?

To use MATCH for comparing two columns in Excel, you’d control the function to search for a specific item from the first column within the second column. Here’s what you’d do in a nutshell: Set your lookup value to be a cell reference from the first column. This is the value MATCH will look for in the second column. Define the lookup array to be the range of the second column. Specify the match type as 0 for an exact match, which is often what you’re after when comparing columns. Apply the formula across all relevant cells in the first column to check for each value’s presence in the second column.

Here’s a quick formula example, assuming you’re comparing Column A to Column B: =MATCH(A2, B:B, 0)

Drag this formula down along Column A, and you’ll see results indicating where in Column B each value of Column A can be found, or #N/A if there’s no match.

Can I Find Partial Matches with the MATCH Function in Excel?

Yes, even though the MATCH function itself looks for exact matches by default, you can gear up Excel to seek out partial matches. This can be a game-changer when working with data that contains similar but not identical entries. Cue the wildcard characters, the asterisk (*) and the question mark (?), for partial matches.

For instance, if you’re comparing company names, and you want to find “JPMorgan” even when it’s listed as “JPMorgan Chase,” an asterisk can help: =MATCH("*"&"JPMorgan"&"*", Range, 0)

The asterisks tell Excel to find any cell where “JPMorgan” appears, surrounded by any number of characters. Just remember, MATCH and wildcards can be a slightly more complex combination, so be extra mindful of what you’re looking for to prevent inaccurate matches.

What Are Some Alternatives to the MATCH Function for Comparing Lists?

While the MATCH function is quite the tool for comparing lists in Excel, one size doesn’t fit all in the data analysis wardrobe. Depending on the task at hand, VLOOKUP, INDEX, and the newer XLOOKUP might better suit your needs.

VLOOKUP, the veteran, takes a lookup value and scans down the first column of a specified range to return a value from the same row. It’s great when you need more than just the position and want the actual data. However, it’s limited to searching only to the right.

INDEX and MATCH can be paired for more flexibility, with INDEX returning the value at a specific location in a range, and MATCH providing the row or column number.

And then there’s XLOOKUP, Excel’s latest couture, designed to eliminate VLOOKUP’s limitations. XLOOKUP can look in any direction—up, down, left, or right—and it handles missing values more gracefully.

Picking the right function is all about the context of your comparison chore. Quick matches? Go with MATCH. Data retrieval? VLOOKUP or INDEX with MATCH. The utmost flexibility? XLOOKUP is your ace.