CODEWITHSUNDEEP
excelexcel-formula
Jacob RRBV
Jacob RRBV

Reputation: 21

How to identify the last data point in a row?

The below question was already asked by another user. I have a variation of the same question. I need to know the column header for the last data value if multiple data points exist for that row. The below formula will return the first data point <>"" but I need the last data point that is <>"". Please non-VBA formulas only.

=IF(COUNTA($E2:$T2)=0,"",INDEX($E$1:$T$1,MATCH(TRUE,INDEX($E2:$T2<>"",0),0)))

Click to see example of needed result Click to see example of needed result

Update 1:

Because my question is not clear, so I post second example

Example 2

Because row 2, there is value 1 at Item 04 so the Get Item Name = Item 04. It is random and have a large number of columns (500).

The problem:

I would like to have a way to get a column header if there is any value input to the cells under that header. Please note that if at row 2 and column 1 has value, then other cell of row 2 will not have any value (other than 0).

It is hard to explain the problem in words so I have created an example.

Upvotes: 0

Views: 1234

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

This will work in all cases to get the last numeric data point in a row and to return the column header...even if there are blank values in-between data points:

=LOOKUP(2,1/ISNUMBER(A5:J5),A$1:J$1)

Note: this assumes the data are in columns A through J, and that the column headers are in the first row. Adjust accordingly.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

If your data starts in A1 then put this in A2 and copy down:

=OFFSET($A$1,0,COUNTA(B2:F2))

Upvotes: 0

Related Questions