How to Cross-Reference Excel Data Across Multiple Columns
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.

- 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.
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).
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.
Click on an empty cell where you want the resulting document numbers from column B to appear.
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.
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 INDEX and SMALL (For Older Versions)
Use this solution if you are running Excel 2019 or earlier, as these versions do not support modern dynamic array functions.
Highlight Matched References with Conditional Formatting
Use this visual method alongside your formulas to instantly spot where the searched document reference is located within the large data matrix.
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. Open the Dataset: Launch WPS Spreadsheet and open your document containing the multi-column records.
- 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. Spill the Results: Press Enter to instantly execute the dynamic array. WPS Spreadsheet will automatically spill all matching document numbers down the column.
- 4. Apply Visual Formatting: Go to the Home tab, use Conditional Formatting to set your matrix highlight rule, and easily view your mapped data.

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.




