How to Compare Cells and Return a Matching Name in Excel
Question details
Check a row for a specific numerical value (e.g., 1) and dynamically return the corresponding name from a related name range or header row.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user needs to extract specific column headers or assigned names dynamically when a target value is identified in a horizontal data range.
- Observed behavior
- The user aims to output either the first matched name or a concatenated string of multiple matched names based on the presence of the value '1'.
Ensure that the range containing your header names perfectly matches the size and layout of the data range you are checking to prevent #VALUE! errors.
Use XLOOKUP to Find the First Matching Name
XLOOKUP is the most straightforward function to locate a specific value in a row and return the corresponding name from the header row.
The XLOOKUP function allows you to search horizontally across a data array and pull a corresponding value from another array of the same size. This method is ideal when you only need to find the very first occurrence of a specific value (like 1).
Click on the cell where you want the returned name to appear.
Type `=XLOOKUP(1, C5:G5, C$3:G$3, "Not found")` into the formula bar.
Press Enter. The formula will search the range C5:G5 for the number 1, and return the corresponding name from C3:G3.

Use FILTER and TEXTJOIN for Multiple Matches
If the search row contains the target value multiple times, use FILTER combined with TEXTJOIN to extract and list all corresponding names.
Process Multiple Rows Dynamically with BYROW
Use the BYROW and LAMBDA functions in modern Excel to apply your matching logic across multiple rows simultaneously.
Use WPS Spreadsheet to Perform Advanced Lookups Easily
WPS Office provides a fully featured Spreadsheet application that natively supports advanced dynamic array functions like XLOOKUP, FILTER, and TEXTJOIN. Process complex data lookups entirely for free while maintaining full compatibility with your Excel files.
- 1. Open your Excel workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the data.
- 2. Apply dynamic functions natively: Select your target cell and use `=XLOOKUP()` or `=FILTER()` exactly as you would in Microsoft Excel.
- 3. Save with complete compatibility: Press Enter to instantly return the matching names, and save your document with full functionality preserved.

Frequently Asked Questions
Why does XLOOKUP return an error when searching my data?
If XLOOKUP returns a #VALUE! error, it is typically because your lookup array (e.g., C5:G5) and return array (e.g., C3:G3) are not the exact same size. Ensure both ranges cover the same number of columns.
Can I use INDEX and MATCH to return the matching name instead?
Yes. If you are using an older version of Excel or WPS Spreadsheet that does not support XLOOKUP, you can use `=INDEX(C$3:G$3, MATCH(1, C5:G5, 0))` to find the first occurrence of 1 and return the corresponding header.
How do I handle cases where no match is found without getting an #N/A error?
In XLOOKUP, you can provide a custom text string in the fourth argument, such as 'Not found'. If you are using older functions like INDEX/MATCH, wrap your formula in the IFERROR function, like `=IFERROR(INDEX(...), "No match")`.




