To do this in VBA, you can call the Excel VLookup function from withi
VBA

Assume A1 has your list box,
the cell link is B1
you have a range with your lookup table 1 banana, 2 apples etc

the this code will give you what you want

Sub test()
Dim myRange As Range
y = Range("B1").Value
Set myRange = Range("Table")
X = Application.WorksheetFunction.VLookup(y, myRange, 2)
End Sub

Regards

Mat

--
Mallyca
-----------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...fo&userid=3551
View this thread: http://www.excelforum.com/showthread.php?threadid=56178