How to Compare Two Lists in Excel and Pull Matching Data

Need to compare two lists in Excel and return related information for matching records? For example, you may have a master product list containing product IDs and names, while another list contains only the product IDs you need to check. Instead of manually searching the master list, you may want to match each ID and automatically return the corresponding product name, price, status, or other information.

Compare two lists in Excel and pull matches in Kutools for Excel's Formula Helper Plus makes this task easier by building the required INDEX MATCH formula for you. Available in the Local library under the Compare category, it lets you specify the value to look up, where to search, which data to return, and the match type without manually constructing the formula.

What Compare two lists in Excel and pull matches can do

Benefits of comparing and pulling matches with Formula Helper Plus

How to compare two lists and pull matching data

• Compare two lists and return matching data with Formula Helper Plus
• Understand the four formula arguments
• How the generated INDEX MATCH formula works

Choose the right MatchType

Tips for accurate matching

Practical use cases

About Formula Helper Plus

FAQ

What Compare two lists in Excel and pull matches can do

Compare two lists in Excel and pull matches is designed for situations where you need to search for a value in one list and return related information from another column or range. It is particularly useful for IDs, codes, names, and other records that appear in separate lists.

Benefits of comparing and pulling matches with Formula Helper Plus

Although the same result can be created manually with INDEX and MATCH, Formula Helper Plus simplifies the setup and helps reduce mistakes when working with multiple ranges and arguments.

How to compare two lists and pull matching data

Compare two lists in Excel and pull matches helps you match lookup values against a source list and return the related data with an automatically generated INDEX MATCH formula. This section walks through the setup, explains the required arguments, and shows how the formula works.

In the example below, Product IDs in a Lookup List are matched against a Master Product List to return the corresponding Unit Prices.

Compare two lists and return matching data with Formula Helper Plus

Use this method to match a list of lookup values against a source table and return the corresponding data for all matches at once. In this example, Product IDs in the Lookup List are matched against the Master Product List to return the related Unit Prices.

1. Select the first cell in the Returned Unit Price column where you want the results to begin.

2. Click Kutools > Formula Helper > Formula Helper Plus.

3. In Formula Helper Plus, make sure the Local tab is selected. Under Category, click Compare, then select Compare two lists in Excel and pull matches.

Tip: You can also enter keywords such as compare two lists or pull matches in the search box to quickly find the formula.

4. Specify the four arguments. In this example, select the Unit Price range in the Master Product List as ReturnRange_Abs, the full Product ID range in the Lookup List as LookupValue, the Product ID range in the Master Product List as LookupRange_Abs, and enter 0 for MatchType to perform an exact match.

5. Click OK or Apply. Because the full Product ID range is selected as LookupValue, the matching Unit Prices are returned and spilled into the corresponding cells automatically.

Tip: If you select only one cell as LookupValue, the formula returns a single result. You can then fill the formula down to look up the remaining values. To return results for the entire list at once, select the full lookup range instead.

Result: The corresponding Unit Prices are returned for the entire Lookup List in one operation. If a Product ID cannot be found in the Master Product List, the corresponding result displays #N/A.

Understand the four formula arguments

The four argument boxes determine which values are searched for, where Excel searches for them, and which corresponding values are returned. In this example, multiple Product IDs are matched against the Master Product List and their related Unit Prices are returned.

Tip: ReturnRange_Abs and LookupRange_Abs should cover corresponding rows. In this example, Product IDs are in A5:A16, so the related Unit Prices are selected from C5:C16.

How the generated INDEX MATCH formula works

Formula Helper Plus combines INDEX and MATCH based on the arguments you specify. Its generic structure is:

=INDEX(ReturnRange_Abs, MATCH(LookupValue, LookupRange_Abs, MatchType))

In this example, Product IDs in the Master Product List are stored in A5:A16, Unit Prices are in C5:C16, and the Product IDs to look up are in E5:E12. The generated formula is:

=INDEX($C$5:$C$16,MATCH(E5:E12,$A$5:$A$16,0))

The formula works in two parts:

• MATCH searches for each Product ID in E5:E12 within A5:A16 and returns the corresponding positions.
• INDEX uses those positions to return the matching Unit Prices from C5:C16.

Because multiple Product IDs are supplied as the lookup values, the formula can return the corresponding Unit Prices for the entire lookup list at once. For example, P1003 returns $189.00, while P1013, which does not exist in the Master Product List, returns #N/A.

Choose the right MatchType

MatchType determines how MATCH searches for the lookup value. In most list-comparison tasks involving IDs, codes, or names, you will normally want an exact match.

0 – Exact match

Use 0 when the lookup value must match a value in the lookup range exactly. This is the most common choice for Product IDs, Order IDs, Employee IDs, SKUs, customer codes, email addresses, and similar identifiers.

1 – Exact match or next smaller value

Use 1 when you need an approximate match that returns the position of the largest value less than or equal to the lookup value. The lookup range should be arranged in ascending order.

-1 – Exact match or next larger value

Use -1 when you need an approximate match that returns the position of the smallest value greater than or equal to the lookup value. The lookup range should be arranged in descending order.

Tips for accurate matching

If the formula does not return the result you expect, first check the lookup values and ranges. Small differences in the source data can prevent otherwise similar-looking records from matching correctly.

• Keep the lookup and return ranges aligned: The ranges should cover corresponding rows. For example, use A2:A100 together with B2:B100, rather than ranges with different starting or ending rows.
• Use MatchType 0 for IDs and codes: Exact matching is usually the safest choice for Product IDs, Order IDs, SKUs, customer codes, and other identifiers.
• Check for extra spaces: Values such as P1001 and P1001 may look identical but contain different text because of leading or trailing spaces.
• Keep number and text types consistent: A numeric value such as 1001 and the text value "1001" may not behave the same way during lookup operations.
• Check values that return #N/A: If an exact match cannot be found, verify that the lookup value exists in the lookup range and that the values use consistent formatting and data types.
• Watch for duplicate lookup values: With an exact MATCH, the formula uses the first matching position it finds. This formula is not designed to return every matching record when duplicate lookup values exist.

Practical use cases

Comparing two lists and returning related information is useful whenever one dataset contains identifiers and another contains the details associated with those identifiers.

Pull product details from a master list

Match a Product ID or SKU against a master product list and return the corresponding product name, price, category, supplier, or other product information.

Match order IDs and return order information

Compare Order IDs from one report with a master order list to return payment status, shipping status, delivery dates, tracking numbers, or other order details.

Match employee IDs and return employee information

Use Employee IDs to retrieve corresponding employee names, departments, managers, office locations, or other HR information.

Match customer IDs and return contact details

Compare Customer IDs against a customer list to return names, email addresses, phone numbers, account types, or other related information.

About Formula Helper Plus

Formula Helper Plus in Kutools for Excel is a formula library that helps you find, understand, and insert useful formulas through a guided interface without having to remember their full syntax. Each formula provides labeled argument boxes together with instructions that explain the required inputs.

The interface includes Local and Online formula libraries. This article focuses on the Local library, where built-in formulas are organized into categories such as Calculate, Compare, Count, Date, Lookup, and Text. You can browse formulas by category or enter keywords in the search box to quickly find a formula by name or purpose.

The Local library also lets you create your own categories and formulas for calculations you use regularly, as well as edit, copy, and organize them directly in Formula Helper Plus. The Online library provides additional formulas that you can browse separately.

🗂️ Category tools in Local

Use the category tools in the Local library to browse built-in formula groups and organize the formulas you create yourself.

1. Category list: Browse formulas by task, including Calculate, Compare, Count, Date, Lookup, and Text.
2. Add category: Create a custom category for organizing formulas that you add to the Local library.
3. Rename category: Select a custom category and rename it to better match the formulas it contains.

🧮 Formula tools in Local

Use the formula tools in the Local library to search for formulas or create and manage your own reusable formulas.

1. Search: Enter keywords in the search box to quickly find formulas by name or purpose.
2. Add formula: Create a custom formula and assign it to a category. When defining the formula, enclose each variable argument in # signs, such as #Range#.
3. Edit formula: Select a custom formula and modify its formula expression, category, or description.
4. Copy formula: Create an editable copy of the selected formula, which is useful when you want to build a new formula based on an existing one.
5. Upload formula: Upload a formula from the Local library to share it with the Online formula library.

🔤 Display controls

The display controls are available in both the Local and Online libraries. Use them to adjust the text size in the right-hand information pane for easier reading.

1. Increase font size: Enlarge the formula description, generic formula, arguments, and other information displayed in the right-hand pane.
2. Decrease font size: Reduce the text size in the right-hand pane when you want to view more information at once.

FAQ

What does Compare two lists in Excel and pull matches do?

It looks for a specified value in one range and returns the corresponding value from another range. This is useful when one list contains IDs or other lookup values and another range contains the related information you want to retrieve.

Where can I find Compare two lists in Excel and pull matches?

Open Formula Helper Plus, select the Local tab, and choose the Comparison category. Then select Compare two lists in Excel and pull matches. You can also enter keywords such as compare two lists in the search box to find it quickly.

Which MatchType should I use?

For most comparisons involving IDs, codes, names, or other values that should match exactly, use 0. Use 1 or -1 only when you intentionally need an approximate match and the lookup range is sorted appropriately.

Can the return column be to the left of the lookup column?

Yes. Because INDEX and MATCH use separate lookup and return ranges, the return column can be located either to the left or right of the lookup column.

What happens if no matching value is found?

With an exact match, the formula returns #N/A if the lookup value cannot be found. Check whether the value exists in the lookup range and whether extra spaces or inconsistent number and text formats are preventing a match.

What happens if the lookup value appears more than once?

The formula returns the value associated with the first matching position found by MATCH. It is not designed to return all records associated with duplicate lookup values.

Is this the same as finding duplicates between two lists?

No. This formula uses a value from one list to find a corresponding record and return related information from another range. It is not simply used to identify or highlight duplicate values.

Will the returned result update when the source data changes?

Yes. The result is inserted as an Excel formula, so changes to the referenced source values can be reflected when Excel recalculates the worksheet.