# Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Here’s an example of identifying the column, and then the value of the header in row 4 of that column, for the first cell in a row that is not a zero.

In cell N5 and copied down as needed is the array formula
`=INDIRECT(ADDRESS(4,MATCH(TRUE,B5:M5<>0,0)+1))`

The “+1” in the formula for the `MATCH` function is to account for the column number in the array being returned, with column A not a part of the array. So getting the grid’s actual column number along with known header row 4 produces the map to finding that intersecting cell’s month label.

Recall, array formulas are entered by simultaneously pressing the `Ctrl+Shift+Enter` keys, not just `Enter`. The curly braces are not entered by you. Excel places them automatically when the array formula is properly applied.

###### 7 comments on “Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row”
1. tony says:

Works great. I used it for the first item in a column

={INDEX(J\$18:J\$30,MATCH(TRUE,J\$18:J\$30″”,0))}

well, simplified a bit, but now I want to find the last item in the columns
eg
bill null
null jim
geoff daisy
fred null

I want bill/fred, then jim/daisy. Up to 15 in each column actually.

ideas?

• Tom Urtis says:

Thanks for following my Excel blog. I am not clear about what your list looks like. It looks like you represent two columns in your depiction but I cannot comprehend the logic you seek, such that bill and fred (2 items, not just one per my post), and jim and daisy (again 2 items, and this time in the middle of the second column) are returned. Please try re-explaining what your sheet looks like, and what your expected results are.

– Tom

2. Meghan says:

Hi Tom,

Thanks for this post–I got it to work as described. Now I am trying to use the same operation but return the data in a cell on a different sheet rather than at the end of a column. How do I change the header reference to accommodate? Then I want to be able to return the next non-zero, and so on.

Specifically, on sheet 1, I will have a single product ID and want to return the appropriate headers (from sheet 2) to match that product’s SKU configuration.

IE:
Option 1 (header returned from Sheet2) — Option 3
SKU1

Option 2 is skipped because it is not marked with an X below.

Sheet 2 contains my table of each SKU configuration in the rows, and each column has a specific option and an “x” if this applies to the Product ID.

IE: Option 1 — Option 2 —- Option 3
SKU1 X — X
SKU2 – X X

I hope this is not a totally convoluted explanation!

Thanks,
Meghan

• Tom Urtis says:

Yikes – – Having a rough time visualizing this. You can send me your workbook if you want, with expected results so I can see what you are referring to.

3. Kaustubh says:

How can i use this to lookup the headers of all non zero items in a row?

4. Richard says:

Hello, thanks for the info by the way! It has helped me, but I did get stuck on something. For column N, it returns as custom instead of the Date that I put, and it won’t let me sort by Date, only alphabetically.

• Tom Urtis says:

I don’t understand what you mean, “it returns as custom instead of the date that I put”. Please provide an example of what you input, what result you get, and what result you want instead.