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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
9 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.

  5. Victoria says:

    It is 4am. I’ve been up since 11 looking for this answer.
    THANK YOU. Finally!

Leave a Reply

Your email address will not be published. Required fields are marked *

*