# Excel formula help - vlookup maybe?

not sure if vlookup is what i want here, but here's what i need:

A    B    C    D
5
6          1
7                 2
8     3
9                 4

i need a formula that will look at a value (X) and compare to table above.  If X is in column A, set = X.  If X is in column B, C, or D, set = to the corresponding value in column A.  So, if X=2, set = 7 or if X=5, set = 5.
###### Who is Participating?

Commented:
Hello darren,

If your table is in A1:D5 and the "lookup value" is in A8 then you can use this "array formula"

=INDEX(A\$1:A\$5,SMALL(IF(A\$1:D\$5=A8,ROW(A\$1:D\$5)-ROW(A\$1)+1),1))

confirmed with CTRL+SHIFT+ENTER

If you want to avoid error values if the lookup value isn't in the table then switch to a version like this

=IF(COUNTIF(A\$1:D\$5,A8),INDEX(A\$1:A\$5,SMALL(IF(A\$1:D\$5=A8,ROW(A\$1:D\$5)-ROW(A\$1)+1),1)),"No Match")

That will return "No match" if lookup value doesn't exist

see attached

regards, barry
26853833.xls
0

Author Commented:
exactly what i was looking for, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.