Function Problems

How to Compare Cells and Return a Matching Name in Excel

John WilsonJohn Wilson Nov 11, 2026 870 views

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.

How to Compare Cells and Return a Matching Name in Excel
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'.
Before you start

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.

Solution 1Recommended

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).

1
Select the destination cell

Click on the cell where you want the returned name to appear.

2
Enter the XLOOKUP formula

Type `=XLOOKUP(1, C5:G5, C$3:G$3, "Not found")` into the formula bar.

3
Execute the formula

Press Enter. The formula will search the range C5:G5 for the number 1, and return the corresponding name from C3:G3.

Use XLOOKUP to Find the First Matching Name
Handling missing data: The fourth argument in the XLOOKUP function allows you to specify a custom message (like "Not found") if the target value does not exist in the searched row.
Powerful Excel Alternative

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. 1. Open your Excel workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the data.
  2. 2. Apply dynamic functions natively: Select your target cell and use `=XLOOKUP()` or `=FILTER()` exactly as you would in Microsoft Excel.
  3. 3. Save with complete compatibility: Press Enter to instantly return the matching names, and save your document with full functionality preserved.
Fully compatible with Microsoft Excel (.xlsx) formats.Native support for advanced functions like XLOOKUP, FILTER, and TEXTJOIN.Free, lightweight, and easy to use with a familiar tabbed interface.Built-in multi-platform syncing across Windows, Mac, iOS, and Android.
microsoft office alternative - wps office

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")`.