Find a Value in an Excel Array and Return Its Column Header
Question details
Find a value in a two-dimensional Excel range and return the header of the column containing it.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Values are in K12:AD25, and their column identifiers are in K11:AD11. The user wants to search for a value such as 207KH.
- Observed behavior
- The desired result is the row 11 identifier for the column containing the search value.
Check that the headers are in K11:AD11 and the values are in K12:AD25. Enter the value to find in A1 and ensure it appears only once in the search range.
Use INDEX and SUMPRODUCT to return the matching column header
Use this formula when the search value has exactly one match in the two-dimensional range.
SUMPRODUCT finds the matching column position, and INDEX returns the header at that position. The match-count check prevents a misleading result when there are no matches or more than one match.
Type the value to find, such as 207KH, in cell A1.
Select an empty cell and enter =IF(SUMPRODUCT(--($K$12:$AD$25=$A$1))=1,INDEX($K$11:$AD$11,1,SUMPRODUCT(($K$12:$AD$25=$A$1)*(COLUMN($K$11:$AD$11)-COLUMN($K$11)+1))),"No match or multiple matches").
Press Enter. The cell displays the matching column's row 11 header, or a message if the value occurs zero or more than one time.

Look up an Array Value in WPS Spreadsheets
Use INDEX and SUMPRODUCT in WPS Spreadsheets to search a two-dimensional range and return its column header.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 2. Enter the search value: Type the value to find in cell A1 and confirm that the headers and values are in K11:AD11 and K12:AD25.
- 3. Enter the formula: Select an empty cell and enter =IF(SUMPRODUCT(--($K$12:$AD$25=$A$1))=1,INDEX($K$11:$AD$11,1,SUMPRODUCT(($K$12:$AD$25=$A$1)*(COLUMN($K$11:$AD$11)-COLUMN($K$11)+1))),"No match or multiple matches").
- 4. View the header: Press Enter to display the matching column header or the match-count message.

Frequently Asked Questions
Why does the formula say there is no match or more than one match?
The formula counts matching cells in K12:AD25. Check the value in A1 for spelling or extra spaces, and check the range for duplicates.
Will the formula work if the value appears twice in the same column?
No. It requires exactly one matching cell, even when both matches are in the same column. Remove or resolve the duplicate before using the formula.
How do I use the formula with a different range?
Replace each reference to K12:AD25 with your value range and each reference to K11:AD11 with its header range. Also replace K11 in COLUMN($K$11) with the first header cell.
Do I need VBA to return the column header?
No. Enter the INDEX and SUMPRODUCT formula in a worksheet cell and press Enter; no macro is required.




