Return Last Text value in Same Row in Excel

**mdispbee** · 11-01-2011, 01:28 PM

I have a worksheet in Excel 2010 that has the following columns:
ITEM (text)
NOTES (text)
LAST REVIEW RESULT **This is the one I am having trouble with**
WEEK 1 (text returned from a vlookup to another sheet)
WEEK 2 (text returned from a vlookup to another sheet)
WEEK 3 (text returned from a vlookup to another sheet)
WEEK 4 (text returned from a vlookup to another sheet)
ETC
ETC
ETC

In the "WEEK" columns, I have an alpha numeric field (text in Excel) with a Job ID (random values-no order). A value will only appear in the column if there is a JOB ID for that ITEM for that WEEK. (So not all WEEK columns will have values).

I want the LAST REVIEW RESULT column to return the text value from the last cell with a value in that row. So if WEEK 10 in row 4 is the last week to show any entry in row 4, I want the LAST REVIEW RESULT (column C) to return the value from WEEK10 in column 4.

I can't for the life of me figure out how to make this work! Can anyone help? Thank you!

**NBVC** · 11-01-2011, 01:35 PM

Try something like:

=LOOKUP(2,1/(D2:X2<>""),D2:X2)

copied down

where D2:X2 is the range of the first set of values to process...

**Marcol** · 11-01-2011, 01:41 PM

And another way?
In C2

Please Login or Register  to view this content.

Hope this helps

**mdispbee** · 11-01-2011, 01:53 PM

Thank you, both! Turns out that part of my problem was that someone had saved some of the job codes as dates instead so I was getting some funny results. Converted to text and it's perfect! Thank you!

**NBVC** · 11-01-2011, 01:54 PM

I would have suggested something similar, Marcol, but if I am correct in assuming the OP has VLOOKUPs returning results... and I assume that if there is no match the OP would return a blank.... if that is true, then the MATCH(REPT("z",255)... may not work.