I have scoured the internet but come up empty.

I am looking for a formula that will be in a single cell (I don't want to have to fill it down the side of the table for instance) that will return the header of the first column that has a value in it, from left to right. If there's a value in the last cell of the first column, for instance, I want that header returned rather than moving to column 2.

I have tried nested xlookups and index/match combinations and just can't find anything that will be in a single cell.

Any help is appreciated!!

EDIT: Yup, let me be more clear. My apologies!

Here is an excerpt of my table. In this instance, I would want the result to be Sep-21 (a reference to the cell is also fine).

I know I can easily drag the formula in A1 down and pull the column header of the first cell in that row with a value. What I want is to reference this on another sheet, so I don't want to have to do that. I want one formula in one cell to pull this information, if possible. If I can't do it that simply, maybe I have to do some other wizardry and hide a row/column or something and fill the formula down that way and then pick the latest date, but I'd like to avoid that if possible. One cell would be much more elegant!

As for what I've tried, all my attempts are pretty messy and incomplete, so I don't know if it would be helpful to paste them here, but essentially I've tried to use an xlookup where the lookup_array is B2:D6, and then the return_array is the header, but that doesn't seem to work. I guess the lookup_array has to be a single column/row? Something like:

xlookup(ISBLANK(FALSE), B2:D6, B1:D1)

It's kind of like an inverse xlookup, lol.

EDIT2: I've done some more messing about and have come SO CLOSE! Now my problem is with structured references in a table and trying to exclude my first two columns.

First, the solution that does work without structured references (assuming the table starts in A1):

=IFERROR(INDEX(B1:D1,SUMPRODUCT(MIN(IF((B2:D6<>0)*(COLUMN(B2:D6)) > 0, ((B2:D6<>0)*(COLUMN(B2:D6)))))-COLUMN(A1))), "error")

But like I said, my structured references are getting in the way now. I want to pull all the data in my dynamic table, except for the first two columns.

I know this is a completely different problem now, my apologies for that.

CodePudding user response：

The solution (assuming the table starts in A1):

=IFERROR(INDEX(B1:D1,SUMPRODUCT(MIN(IF((B2:D6<>0)*(COLUMN(B2:D6)) > 0, ((B2:D6<>0)*(COLUMN(B2:D6)))))-COLUMN(A1))), "error")

CodePudding user response：

If your data is in row 2:

=INDEX(1:1, 1, MATCH("", 2:2,-1))

should work