Index function to return value of row above

[joyhampton]

Hello,

If I have a list of names in a range

Red
Blue
Pink
Orange

indicated a sequence... (pink always comes before orange)

and I have a cell in another sheet with value "Orange"... in another cell I'd like to return the value "Pink".
I need to lookup "orange" in the sequence and return the value of the cell in the row above it.

Thanks for your help!

[TMS]

Something like:

Please Login or Register  to view this content.

This copes with the scenario that Red is selected (and Red is in row 1) and if the colour is not found in the table.


This example is all on one sheet; just adjust to refer to the appropriate sheet.


Regards, TMS

[daffodil11]

Assuming Red, Blue, Pink, Orange in column A on tab1.
Assuming Red, Blue, Pink, Orange in column A on tab2.

tab1, b1 =

Please Login or Register  to view this content.

[Ron Coderre]

With
A1:A4 containing your list
Red
Blue
Pink
Orange

and
B1: a value to find...Pink

This regular formula returns the value prior to the matched value...or..If Red..returns Red

Please Login or Register  to view this content.

Is that something you can work with?