Thanks to all for the help, the index function worked and now I remember
reading the ascending order deal, thoes numbers all come from different
lookup functions so i would have had to add another table. Index works
perfectly and I think I may be done, Thanks alot

"Teethless mama" wrote:

=INDEX(Member,MATCH(B2,Weight,0))


"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