Get the column number of the last non-empty cell within a range (if values are not unique)

Question

I have the following Excel-Spreadsheet:

     A  B  C    D       E       F       G      H     I      J     
1
2
3
4              200     150     80     300                   4
5              200     150     80     150                   2
6
7

In Range J4:J5 I have the following formulas:

J4 = MATCH(LOOKUP(2,1/(D4:I4<>0),D4:I4),D4:I4,0)
J5 = MATCH(LOOKUP(2,1/(D4:I4<>0),D4:I4),D4:I4,0)

With this formula I want to identify the last non-empty cell within the range and get the the column number of it back. All this works perfectly in Cell J4.

However, in Cell J5 I get back number 2 instead of number 4. As far as I can see the reason for this is that in Range D5:I5 the values are not unique (150 appears two times).

What do I need to change in my formula to always get the column number of the last non-empty cell no matter if the values are unique or not?

The answer from this question does not help because my range is not starting in Column A.
The range can be anywhere on the sheet.

Answer 1

Alternatively, you can adjust your existing LOOKUP function as follows:

=LOOKUP(2,1/(D4:I4<>0),COLUMN(D4:I4)-MIN(COLUMN(D4:I4))+1)

Answer 2

You only need MATCH:

=MATCH(1E+99,D4:I4)

if you are looking for text then:

=MATCH("zzz",D4:I4)

Or either:

=MAX(IFERROR(MATCH(1E+99,D4:I4),0),IFERROR(MATCH("zzz",D4:I4),0))

---

The issue is that the LOOKUP returns the actual value, in the case of the error it returns 150 to the MATCH:

MATCH(150,D4:I4,0)

Which will return the first match location.