Function Problems

How to Match and List Names Across Excel Sheets with a Formula

Emma Brown Oct 20, 2026 868 views

Question details

The user needs to match specific text criteria across multiple columns in one sheet and output all corresponding associated records horizontally into another sheet.

How to Match and List Names Across Excel Sheets with a Formula

Product: Excel
Device & OS: not provided
Scenario: Looking up records (e.g., mortgage companies) distributed across several columns on a source sheet to retrieve associated values (e.g., homeowners) to a target sheet.
Observed behavior: To achieve the goal state, the user requires a combined formula handling multiple OR criteria, transposing the array horizontally, and cleaning up extra spaces that cause matching errors.

Before you start

Verify that the data ranges in your formula match the exact size of your source data, and ensure your spreadsheet software supports dynamic array functions like FILTER.

Solution 1Recommended

Use FILTER, TRANSPOSE, and TRIM Functions

Combine these dynamic array functions to filter data across multiple columns, clean up trailing spaces, and output the results horizontally.

The FILTER function can look up matches across various columns by adding criteria together with the '+' operator, which acts as an OR logic. Applying TRANSPOSE changes the vertical output to horizontal, and TRIM prevents matching failures caused by invisible extra spaces.

Select the output cell

Navigate to your target sheet (e.g., Sheet2) and select the cell adjacent to the lookup value where you want the results to begin displaying.

Enter the combined formula

Type the following formula, adjusting the cell references to match your data ranges: =TRANSPOSE(FILTER(Sheet1!$A$2:$A$5,(TRIM(Sheet1!$B$2:$B$5)=TRIM(Sheet2!A2))+(TRIM(Sheet1!$C$2:$C$5)=TRIM(Sheet2!A2))+(TRIM(Sheet1!$D$2:$D$5)=TRIM(Sheet2!A2))))

Apply and review results

Press Enter. The dynamic array formula will automatically spill the matching results into the adjacent cells horizontally. Ensure the destination cells are empty to avoid a #SPILL! error.

Use FILTER, TRANSPOSE, and TRIM Functions

Formula Breakdown: By wrapping both the lookup ranges and the target cell reference in the TRIM function, you guarantee that typographical formatting inconsistencies (like trailing spaces) do not break your data matches.

Advanced Data Filtering in WPS Spreadsheet

Filter and Extract Data Effortlessly with WPS Office

WPS Spreadsheet provides comprehensive support for dynamic array functions like FILTER and TRANSPOSE. Easily extract and organize cross-sheet data without relying on complex macros or scripts.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your cross-sheet data.
2. Enter dynamic array formulas: Type your combined FILTER, TRANSPOSE, and TRIM formulas exactly as you would in other major spreadsheet software.
3. Instantly spill results: Hit Enter, and let WPS Spreadsheet's modern calculation engine automatically spill the matching results horizontally across the required cells.

Fully compatible with Microsoft Excel formulas and dynamic arrays (.xlsx).Lightweight installation and lightning-fast processing for large datasets.Clean and intuitive user interface with built-in data cleanup tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the FILTER formula returning a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no matching results for your lookup value. You can handle this gracefully by adding an optional 'if_empty' argument to the end of the FILTER function, for example: =FILTER(range, criteria, "No Match Found").

Can I output the matched names vertically instead of horizontally?

Yes. If you prefer the extracted data to be listed vertically down a single column, simply remove the TRANSPOSE function from your formula. Use only the FILTER function and its criteria.

Why does my formula fail to find a match when the text looks identical?

Matching errors are frequently caused by invisible leading or trailing spaces, or non-breaking spaces in your text cells. Using the TRIM function inside your formula, as demonstrated in the solution, removes these extraneous spaces and ensures accurate matches.

What does the plus (+) sign do in the FILTER criteria?

In array formulas and the FILTER function, the plus (+) sign acts as a logical OR operator. It allows the formula to check multiple columns simultaneously and return a match if the lookup value is found in any of those specified columns.

Excel & Data Problems