vlookup on cell below

[FJDx]

Is it possible to do a VLOOKUP but instead of returning the value in row
that contains the lookup, it returns the value in the row below?

eg

a b
1 no x
2 yes y
3 ok z


=VLOOKUP("yes",A1:B3,2,FALSE) would return "z" instead of "y".

Th only way I can think of is to add a row header at the top and use the
MATCH function in column A to find the row position of "yes", then use
that in an HLOOKUP in column B. I was hoping there was a simpler way.

 

[Bob Phillips]

Not VLOOKUP, but INDEX & MATCH

=INDEX(B1:B3,MATCH("yes",A1:A3,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi

an alternative

=OFFSET($A$1,MATCH(B7,$A$2:$A$4,0)+1,1)

where no, yes, ok are in cells A2:A4

Cheers
JulieD