LOOKUP result error.
Posted by Mike on January 30, 2002 5:04 AM
In column "S" I have the following formula:
Median(V17:AT17) for my Median Cost (from 3 choices).
From Coulmn Header V13:AT13 runs my Suppliers.
From V17:AT17 runs my quoted "costs" (of which there
are only 3 choices so my Median pulls the middle quoted price).
My formula in U17 reads:
(LOOKUP(S17,V17:AT17,V$13:AT$13).
I read this as What ever shows up in S17, go to the choice in row 13 above and pull the Supplier Name.
The cost in S17 is $3.43 (from Y17).
X13 (Supplier name is "Daria",
Y13 (Supplier name is "Nordt",
Z13 (Supplier name is "Link".
The result from the LOOKUP Fx should be Nordt,
yet it's pulling Daria.
Any reason why?
Line # S U x y Z
13 Supplier (LOOKUP...) Daria Nordt Link
17 $3.43 Daria $4.18 $3.43 $3.25
Posted by Mike on January 30, 2002 7:17 AM
This works whether I use MIN or Max but not Median.
Any idea why?
Posted by Aladin Akyurek on January 30, 2002 7:53 AM
Mike --
Try instead:
(a) =INDEX(V13:AT17,MATCH(S17,V17:AT17,0))
(b) =INDEX(V13:AT17,MATCH(S17,V17:AT17))
please report back the results for both.
Aladin
Posted by Aladin Akyurek on January 30, 2002 8:24 AM
(a) =INDEX(V13:AT13,MATCH(S17,V17:AT17,0))
(b) =INDEX(V13:AT13,MATCH(S17,V17:AT17))
Posted by Mike on January 30, 2002 11:57 AM
* * * * *
your suggestion "a" worked great.
why wouldn't he LOOKUP function work on the Median. Everything was fine when I used Min or Max.
Any feedback on the LOOKUP issue?
Thanks.
Posted by Aladin Akyurek on January 30, 2002 12:40 PM
EXPECTED that.
LOOK at the Help file. The following is from that file:
QUOTE
The values 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.
UNQUOTE
I think that it was accidental that it returned the right value when applying MIN or MAX.
Aladin
Posted by Mike on January 30, 2002 12:58 PM
* * * *
