don't use something like vlookup, use vloopup

Dim res as Variant
res =
Application.Vlookup(Range("A1"),Worksheets("Sheet2 ").Range("A1:E200),4,False
)
if not iserror(res) then
msgbox "Returned value is " & res
End if

or to make it easier to do what you want, use Match instead

Dim res as Variant
set rng = Worksheets("Sheet2").Range("A1:E200")
res = Application.Match(Range("A1"), rng,0)
if not iserror(res) then
set rng1 = rng(res).offset(0,4)
msgbox "value returned is: " & res & " at " & rng1.Address(0,0,xlA1,True)
End if

now you can use rng1 to update the cell.


--
Regards,
Tom Ogilvy


"Cheryl" wrote in message
...
I have a user form that contains a combobox that displays all of our
vendors. When I select a vendor, I want to use VBA to retrieve the data for
that vendor such as address.. How do I do this using something similar to
vlookup on the worksheet. Basically, once I display the data.. I want to be
able to edit it.. such as fixing the address and write back to the excel
table it used to retrieve the information..

Thanks in advance for your suggestions or link to a useful resource where
I can find out how to use it...