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.
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?
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
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
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.
How can i use this to lookup the headers of all non zero items in a row?
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.
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.