PDA

View Full Version : Getting cell coordinate from the last cell with text in it in a ranged row



epd
05-26-2013, 03:18 PM
...I hope that heading made sense.

Refer to the below image.

Hi, I'm trying to have column C display items in row 3 under the TRANS heading based on the last cell in a range that is populated.

So for example, C10 should read E as the last populated cell in range E10:K10 is I10....which falls under heading E.

This is the code I have for D10:
=LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10)

This shows me the value of the last populated cell in that specified range.
Having that code, I assumed OFFSET(LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10),-B10,0,1,1) would work, before realizing that I'm not getting the cell location, but rather the value.

I assume I'm correctly using OFFSET(***,-B10,0,1,1).

To manually get the TRANS sub heading value I would use OFFSET(I10,-B10,0,1,1). But how should my code look if I want it to manually work out the last populated cell in range E10:K10 and then give me the TRANS sub heading value?

http://imageshack.us/a/img850/8243/helpzp.png

Thank you

This has also been posted here (http://www.mrexcel.com/forum/excel-questions/702496-getting-cell-coordinate-last-cell-text-ranged-row.html#post3469701)

epd
05-26-2013, 04:33 PM
Problem has been solved.


In C4, =LOOKUP(2, 1/(E4:K4<>""), E$3:K$3)

http://www.mrexcel.com/forum/excel-questions/702496-getting-cell-coordinate-last-cell-text-ranged-row.html#post3481659

SamT
05-26-2013, 04:55 PM
I don't know how you're using INDEX like that, but, try this formula using the Vector form of LOOKUP
=LOOKUP(2,1/INDEX(E10:K10<>"",0),E10:K10,E3:K3)