Try something like this on Sheet1:

A2: =INDEX(Sheet2!A1:A10,MATCH(B2,Sheet2!B1:B10,0))

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"JD" wrote:

Well yesterday I was lucky and got a quick answer to my question, so I
figured i would try my luck again today.

I have a spreadsheet with alot of lookup and reference functions. I need to
lookup one entry in a list which has some errors in it and return the
contents of the a cell in the same row but another column. The spreadsheet
is like this:

Sheet 1
A B
Member Weight (lbs.)
#N/A 35100

Sheet 2
A B
Member Weight (lbs)
#N/A #N/A
#N/A #N/A
#N/A #N/A
16KCS3 40950
18KCS2 35100
20KCS2 37050
22KCS2 39000
24KCS2 39000
26KCS2 39000
28KCS2 39000
30KCS3 50700

I thoght that it would be easy with just the function in A1:
=LOOKUP(B2,Sheet2!B1:B10,Sheet2!A1:A10)
but it returns #N/A. (I'm looking for it to respond with 18KCS2)

What is causing the error? or is there some other way I should use to find
the member that corresponds to a weight of 35100.

Also will it be a problem if it tried to find the member that corresponds to
39000, I would want it to refer to 22KCS2.

Thanks alot, JD