Search for last cell in range containing data, get value from cell above

[JJGF]

I need to find the last cell in range AM3:BD3 with data (text or number) and get the value from the cell above.
So if the last cell with data in range AM3:BD3 is AP3, get value from cell AP2.

[BadlySpelledBuoy]

Would there be gaps in the data (i.e. blank cells mid range)?

[Ace_XL]

try this array formula
=INDEX(2:2,MAX((AM3:BD3<>"")*COLUMN(AM3:BD3)))
Confirm with ctrl+Shift+Enter and not just Enter

or this non-array formula
=LOOKUP(2,1/(AM3:BD3<>""),AM2:BD2)

[RudiS]

If its VBA code you are after, try:

Please Login or Register  to view this content.

[JJGF]

If its VBA code you are after, try:

Please Login or Register  to view this content.

no, just a formula but thanks for that option.

[JJGF]

try this array formula
=INDEX(2:2,MAX((AM3:BD3<>"")*COLUMN(AM3:BD3)))
Confirm with ctrl+Shift+Enter and not just Enter

or this non-array formula
=LOOKUP(2,1/(AM3:BD3<>""),AM2:BD2)

Yes they both work, I don't like using arrays so I appreciate the non-array solution, I am curious if you can explain how this formula works? =LOOKUP(2,1/(AM3:BD3<>""),AM2:BD2)

[Ace_XL]

Well explained in post #4 of this thread

http://www.excelforum.com/excel-gene...-in-range.html

[JJGF]

Well explained in post #4 of this thread

http://www.excelforum.com/excel-gene...-in-range.html

excellent, thanks