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.
It is 4am. I’ve been up since 11 looking for this answer.
THANK YOU. Finally!
Thank you!!!