1

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.

Michi
  • 4,663
  • 6
  • 33
  • 83
  • Would there be gaps in your data? Say, `200,150,,300`. And are these actually formula results or just typed values? – JvdV Sep 10 '19 at 16:25

2 Answers2

4

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

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

enter image description here

Michi
  • 4,663
  • 6
  • 33
  • 83
Justyna MK
  • 3,523
  • 3
  • 11
  • 25
  • Thanks Justyna for the answer. I think the nice part of this formula is that it even works in case there are words within the range. – Michi Sep 10 '19 at 15:57
  • True, it will also have these identified properly. Thanks for noticing it! – Justyna MK Sep 10 '19 at 15:58
  • The other way around if you want to exclude the non-number values from it you can go with solution from Scott: =MATCH(1E+99,D4:I4) – Michi Sep 10 '19 at 16:00
  • The only thing I would do would be to replace the `-3` with `-MIN(COLUMN(D4:I4))+1` Then if the range changes the user does not need to do the math, just change the range to match the rest. – Scott Craner Sep 10 '19 at 16:02
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))

enter image description here

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81