lookup formula problem

[timtim89]

Hey all

I need to lookup values in one sheet to another sheet based on a matching criteria. The problem is that vlookup needs the "value to be searched for" to be in first column of the target matrix, which is not the case. lookup function fails as well. "values to be searched for" are placed in the second and last column of the target matrix, and first column is the value I want "looked up".

Please see attachment for further clarification of my problem.

Thanks a lot for your help and effort

[martindwilson]

for lookup to work col b of ark2 needs to be sorted in ascending order

6 black
2 blue
3 green
1 red
5 white
4 yellow
or use
=INDEX(Ark2!$A$1:$A$6,MATCH(C2,Ark2!$B$1:$B$6,0))
or even
=VLOOKUP(C2,CHOOSE({1,2},Ark2!$B$1:$B$6,Ark2!$A$1:$A$6),2,FALSE)

[timtim89]

The INDEX+MATCH way solves my problem the best! Thanks a lot!