Formula Errors

Find a Value in an Excel Array and Return Its Column Header

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

Question details

Find a value in a two-dimensional Excel range and return the header of the column containing it.

Find a Value in an Excel Array and Return Its Column Header
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the search value

Type the value to find, such as 207KH, in cell A1.

2
Enter the lookup 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").

3
Check the result

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.

Use INDEX and SUMPRODUCT to return the matching column header
Multiple matches: The formula counts matching cells, even if they are in the same column. Resolve duplicate matches before using it to return a header.
Find column headers without VBA

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
  2. 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. 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. 4. View the header: Press Enter to display the matching column header or the match-count message.
Find a matching column header without VBAOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and cell referencesWork with spreadsheets for free
microsoft office alternative - wps office

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.