Try this array formula** :

=INDEX(D2:D10,MAX((A2:C10=A1)*ROW(A2:C10)-MIN(ROW(A2:C10))+1))

Where A1 = lookup value

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"MagicBill" wrote in message
...
I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I
get
an error value. So how can I type in a W and get it to return a 5, an I
to
return a 9, and so on?