Another Quetion about lookups with errors

[JD]

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

 

[Ron Coderre]

Try something like this on Sheet1:

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

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

XL2003, WinXP

 

[George Nicholson]

Column B needs to be in ascending order, and it isn't.

The Online Help entry for LOOKUP says:

Important! (in red!) The values in lookup_vector must be placed in
ascending order:
...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may
not give the correct value.
Uppercase and lowercase text are equivalent.

It also states:
a.. If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value.
b.. If lookup_value is smaller than the smallest value in lookup_vector,
LOOKUP gives the #N/A error value.
So, with B out of order, when looking for 25100, it finds 40950, stops, and
returns the next smallest result (the 3rd #N/A). That is what Help means by
"..not give the correct value". Technically, it is the *correct* value per
the stated rules, its just not the value *expected*.

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.

As long as 22KCS2 is listed with the 1st occurance of 39000 in B, that's
what will be returned (once B is ordered properly)


HTH,

 

[Teethless mama]

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

 

[JD]

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