lookup & reference

[Soe]

Here is the data

ABC 10
500
DEF 20
300

I like to retrieve value of 500 when my lookup_value is "ABC".
Vlookup can't solve the issue as it can retrieve only data in the same row,
what I need is to offset and retrieve data.
anybody has any suggestion?

 

[Earl Kiosterud]

Soe,

=OFFSET(A1,MATCH("DEF",A2:A5)+1,2,1,1)

Yours is a very nonstandard table. You will not be able to use much useful
Excel functionality like this. Usually, you have one row per record, with
all needed fields in that record.