Excel Formula to Return the First Heading with X or Z
Question details
Return the first column heading for a column where the data contains either x or z.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet has headings in A1:F1 and data in A2:F5, and the user needs the first matching heading when scanning columns from left to right.
- Observed behavior
- The desired result is the heading of the leftmost column containing at least one x or z, such as 4H.
Confirm that the headings and data ranges have the same number of columns. The formulas below look for the exact lowercase text values x and z; adjust the criteria if your worksheet uses different values.
Use a dynamic-array formula in Excel 365
Use this method when your version of Excel supports LET and TOCOL and you want one formula without helper cells.
The formula checks every data cell, keeps the corresponding column heading when it finds x or z, flattens the results from left to right, and returns the first heading found.
Click the cell where you want the matching heading to appear, such as H1.
Type or paste =LET(result,TOCOL(IF((A2:F5="x")+(A2:F5="z"),A1:F1,""),2,TRUE),INDEX(result,1)) and press Enter.
Replace A1:F1 with your heading row and A2:F5 with the corresponding data range, keeping both ranges aligned by column.
Use COUNTIF helper cells in older Excel versions
Use this method when LET and TOCOL are unavailable or when you want to inspect which columns contain the target values.
Find the First Matching Column Heading in WPS Spreadsheets
WPS Spreadsheets can identify the first heading whose column contains x or z with familiar worksheet formulas. Use COUNTIF in a helper row, then combine INDEX and MATCH to return the leftmost matching heading.
- 1. Open the worksheet in WPS Spreadsheets: Open the XLSX or XLS file, then confirm that the headings are in one row and the data is directly below them.
- 2. Create a count for the first column: Click a blank cell below the first data column, such as A6, and enter =COUNTIF(A2:A5,"x")+COUNTIF(A2:A5,"z").
- 3. Fill the count across all columns: Select A6 and drag its fill handle right through the final data column, such as F6.
- 4. Return the leftmost matching heading: Click the output cell and enter =INDEX(A1:F1,1,MATCH(TRUE,A6:F6>0,0)), then press Enter.
Frequently Asked Questions
What does the formula return if more than one column contains x or z?
It returns the heading for the first matching column from left to right. For example, if columns B and E contain a match, the formula returns the heading in B1.
Can I search for uppercase X and Z as well?
COUNTIF is not case-sensitive, so x and X are treated as the same text. If you need case-sensitive matching, use a different formula approach based on EXACT.
Why does the dynamic-array formula return an error?
Check that your Excel version supports LET and TOCOL, and verify that the heading range and data range cover the same columns. For older Excel, use the COUNTIF helper-row method.
How do I search for different values instead of x and z?
Replace "x" and "z" in the formula with your required criteria. For example, use "yes" and "pending" to find the first column containing either of those values.
