Function Problems

How to Return Matching Values from Two Excel Columns for Calculations

Camila MilosovichCamila Milosovich Nov 10, 2026 870 views

Question details

The user needs to extract values that appear in two separate Excel lists and output these exact matches into new cells so they can be referenced in further formulas and calculations.

How to Return Matching Values from Two Excel Columns
Product
Excel
Device & OS
not provided
Scenario
Comparing two data columns to identify common items and pulling those overlapping values into a designated output range dynamically.
Observed behavior
The user requires a formula-based approach to generate a clean list of intersecting values from two ranges that automatically populates output cells without manual filtering.
Before you start

Identify the two column ranges you want to compare. Ensure your data does not contain hidden trailing spaces, as these can prevent exact matches when using lookup and extraction formulas.

Solution 1Recommended

Use XLOOKUP and TOCOL (Microsoft 365 & Modern Excel)

For modern versions of Excel and WPS Office, you can use a dynamic array formula combining TOCOL and XLOOKUP to easily extract intersecting items.

This method uses dynamic arrays to automatically spill the results into adjacent rows. It is the most efficient and readable way to extract matching data in newer software versions.

1
Select the output cell

Click on the top cell where you want the extracted matching values to begin appearing.

2
Enter the dynamic formula

Type the formula =TOCOL(XLOOKUP(firstRange, secondRange, secondRange), 3). For example, if your lists are in A2:A10 and B2:B10, use =TOCOL(XLOOKUP(A2:A10, B2:B10, B2:B10), 3).

3
Apply the formula

Press Enter. The formula will dynamically spill the intersecting values into the cells below, ignoring errors for non-matching items.

Use XLOOKUP and TOCOL (Microsoft 365 & Modern Excel)
Dynamic Updates: If you update the source data in either column, the spilled array of matching values will update automatically.
Efficient Spreadsheet Tool

Easily Extract Matching Values with WPS Spreadsheet

WPS Office fully supports advanced dynamic array functions like XLOOKUP and TOCOL, allowing you to instantly compare lists and extract matching values for complex calculations. It provides a seamless data analysis experience.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your two data columns.
  2. 2. Select an output cell: Click on a blank cell where you want the resulting list to appear.
  3. 3. Enter the lookup formula: Type =TOCOL(XLOOKUP(Range1, Range2, Range2), 3) and adjust the range references to your specific columns.
  4. 4. Extract and calculate: Press Enter to spill the matches, then use these dynamically extracted values directly in your SUM, AVERAGE, or other dependent calculation formulas.
Fully compatible with Microsoft Excel formulas like XLOOKUP, MATCH, and AGGREGATE.Supports dynamic arrays to effortlessly extract and spill list intersections.Free, lightweight, and features an intuitive tabbed interface for managing multiple workbooks.Provides built-in tools for handling duplicates and cross-referencing datasets.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP formula returning an #N/A error?

If XLOOKUP cannot find a match, it defaults to returning an #N/A error. When wrapped in the TOCOL function with the second argument set to 3, these errors are usually ignored. Ensure your ranges are correctly locked with absolute references (e.g., $A$2:$A$10) if you are moving or copying the formula.

Can I use VLOOKUP instead to find matching values?

While VLOOKUP can be used in a helper column to check if a value exists in another list, it cannot natively spill an entire list of matches into a single dynamic column without complex workarounds. XLOOKUP or the FILTER function is much better suited for extracting a clean list of matches.

How do I remove duplicates from the extracted list of matching values?

You can wrap your dynamic formula in the UNIQUE function. For example, using =UNIQUE(TOCOL(XLOOKUP(Range1, Range2, Range2), 3)) will extract the matching values and simultaneously filter out any duplicate entries, leaving you with a clean, unique list.