How to Return Matching Values from Two Excel Columns for Calculations
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.

- 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.
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.
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.
Click on the top cell where you want the extracted matching values to begin appearing.
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).
Press Enter. The formula will dynamically spill the intersecting values into the cells below, ignoring errors for non-matching items.

Use AGGREGATE and MATCH (Legacy Excel)
If you are using an older version of Excel that does not support dynamic arrays, use a combination of AGGREGATE and MATCH to pull matched records row by row.
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. Open your workbook: Launch WPS Spreadsheet and open the document containing your two data columns.
- 2. Select an output cell: Click on a blank cell where you want the resulting list to appear.
- 3. Enter the lookup formula: Type =TOCOL(XLOOKUP(Range1, Range2, Range2), 3) and adjust the range references to your specific columns.
- 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.

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.




