=IF(B2=B50:B53,A50:A53,"NA")

[jamob]

i am trying to take a value from 1 cell and if it matches a value in i list i want to take that matching if it matches that cell then i want to take the value of just that cell. example: A49 has a number typed in 3, in B50:B53 are values 3,6, and 8, and in column A50:A53 has a locations Eastern, Western, Southern. what i want is for cell B49 to take value A50:A53 and depending on cosponsoring B50:B53 i want location.

A49 = 3
B49 = location from A50:A53 which match number in B50:B52
B49 should say = east
A50 = east
A51 = west
A52 =South

B50= 3
B51= 6
B52= 8

any ideas?

[JohnSUN]

As for me formula =OFFSET($A$49;MATCH($A$49;$B$50:$B$52;0);0) work correctly

[mikekaganski]

You shoot faster and better!

[jamob]

Thanks match worked with little tweeking.

[mikekaganski]

Change the order of columns (make key column to be to the left of column with returned answers), and use VLOOKUP with Sorted=0 (to force strict equality).

=VLOOKUP(A49,A50:B52,2,0)

When value not found, you will get Error: Value Not Available. This might be further processed with ISERROR.

You might use LOOKUP without swapping columns, like this:

=LOOKUP(A49,B50:B52,A50:A52)

but this requires sorted column B, and doesn’t allow for strict comparison (i.e. 4 will give you Eastern).