Function Problems

How to Cross-Reference Excel Data Across Multiple Columns

Camila MilosovichCamila Milosovich Nov 11, 2026 869 views

Question details

The user needs to retrieve specific document numbers from a single column by searching for a matching reference value across a multi-column range.

How to Cross-Reference Excel Data Across Multiple Columns
Product
Excel
Device & OS
not provided
Scenario
Searching a multi-column matrix (e.g., columns C to AP) for a designated value, and returning the associated document identifier from a target column (e.g., column B) whenever a match is found.
Observed behavior
Extracting matching data rows cleanly and highlighting the specific cells within the matrix where the search term appears.
Before you start

Ensure your dataset is properly aligned and check which version of your spreadsheet software you are using, as dynamic array functions like FILTER and BYROW are only available in newer versions (Excel 365, Excel 2021, or WPS Office).

Solution 1Recommended

Use FILTER and BYROW (For Modern Spreadsheet Versions)

This is the most efficient method for Office 365, Excel 2021, or modern WPS Office, as it leverages dynamic arrays to extract all matching records automatically.

Dynamic array formulas can process multi-column lookups by combining the FILTER function with the BYROW and LAMBDA functions. This evaluates the entire matrix row by row and filters the main column accordingly.

1
Select the Destination Cell

Click on an empty cell where you want the resulting document numbers from column B to appear.

2
Enter the FILTER Formula

Type the formula: =FILTER($B$2:$B$162, BYROW($C$2:$AP$162, LAMBDA(row, OR(row=C166))), "None") into the formula bar. Replace C166 with the cell containing your specific search value.

3
Execute the Formula

Press the Enter key. The formula will automatically spill the results downward, listing all document numbers that match the search value found in the reference columns.

Use FILTER and BYROW (For Modern Spreadsheet Versions)
Alternative Dynamic Formula: If you prefer not to use LAMBDA, you can also use this dynamic array alternative: =INDEX($B$2:$B$162, UNIQUE(TOCOL(SEQUENCE(ROWS($C$2:$AP$162)) / SEARCH(C166, $C$2:$AP$162), 3)))
Advanced Spreadsheet Functions

Cross-Reference Data Seamlessly with WPS Spreadsheet

WPS Office Spreadsheet provides full native support for modern dynamic array formulas like FILTER, BYROW, and TOCOL, making it incredibly easy to cross-reference multi-column data without complex workarounds.

  1. 1. Open the Dataset: Launch WPS Spreadsheet and open your document containing the multi-column records.
  2. 2. Enter the Lookup Function: Click an empty cell and enter the cross-reference formula, such as =FILTER($B$2:$B$162, BYROW($C$2:$AP$162, LAMBDA(row, OR(row=C166))), "None").
  3. 3. Spill the Results: Press Enter to instantly execute the dynamic array. WPS Spreadsheet will automatically spill all matching document numbers down the column.
  4. 4. Apply Visual Formatting: Go to the Home tab, use Conditional Formatting to set your matrix highlight rule, and easily view your mapped data.
Full compatibility with modern Microsoft Excel formulas and array functionsLightweight architecture handles massive multi-column datasets smoothlyBuilt-in robust conditional formatting to quickly visualize data matchesCompletely free all-in-one office suite for data analysis and daily tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my cross-reference array formula return a #NAME? error?

This usually happens when you try to use newer dynamic functions like FILTER, BYROW, or LAMBDA in an older version of Excel (like Excel 2016 or 2019). If you see this error, switch to the legacy array formula method using INDEX and SMALL.

Do I need to press Ctrl+Shift+Enter for these cross-referencing formulas?

It depends on your software version. If you are using Excel 365, Excel 2021, or the latest version of WPS Office, you simply press Enter and the results will automatically spill. If you are using older versions with the INDEX/SMALL formula, you must press Ctrl+Shift+Enter.

How can I search for partial text matches across multiple columns?

To search for partial matches dynamically, you can combine the FILTER function with the SEARCH or ISNUMBER functions. The provided alternative formula (=INDEX(B2:B162, UNIQUE(TOCOL(SEQUENCE(...) / SEARCH(C166, C2:AP162), 3)))) utilizes the SEARCH function, which inherently allows for partial string matching inside the matrix.