How to use offset for specific value?

[Eric]

Referring to the post under General Question

Does anyone know how to use offset for specific value? such as
Under column A, there is a list of value.
Under column B, there is a list of value.
In cell C1, I input any value listed under the column A, for example "xxx"
is found under cell A6,
In cell D1, return the value "yyy", which is found under cell B6.

Does anyone have any suggestion?
Thank you in advance
Eric Choi

 

[Toppers]

If the value in A are unique, you could VLOOKUP or MATCH:

=VLOOKUP(C1,$A:$B,2,0)

Assuming datastrts in A1:

=OFFSET($A$1,MATCH(C1,$A$1:$A$6,0)-1,1)

If they are not unique, then you cannot (as far as I know) match the data.

You will need to add error conditions to the above to allow for value in C1
not being present.


e.g.

=IF(isna(vlookup formula.),"",vlookup formula)

HTH