Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with LOOKUP function
A1 - "A"
B1 - "B" C1 - "C" A2 - 3 B2 - 2 C2 - 1 D2= is returning the MAX in the A2:C2 array and returning 3 E2 is my LOOKUP function. =LOOKUP(D2,A2:C2,A1:C1) Which I thought was saying, look in the array a2:c2, find the value in d2(3), and return the corresponding value in the a1:c1 array, which would be "A". I would be expecting it to return a value of "A" but it's returning "C" instead. I've tried VLOOKUP and HLOOKUP and haven't be able to get that to work with this either. Any ideas what I'm doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with LOOKUP function
I get the same result as you when testing your data, must have something to
do with the highest value being in the left most column as if you make B the higest value it gives the correct result. Has me stumped as well. May have something to do in the sorting of the numerical data. "J" wrote: A1 - "A" B1 - "B" C1 - "C" A2 - 3 B2 - 2 C2 - 1 D2= is returning the MAX in the A2:C2 array and returning 3 E2 is my LOOKUP function. =LOOKUP(D2,A2:C2,A1:C1) Which I thought was saying, look in the array a2:c2, find the value in d2(3), and return the corresponding value in the a1:c1 array, which would be "A". I would be expecting it to return a value of "A" but it's returning "C" instead. I've tried VLOOKUP and HLOOKUP and haven't be able to get that to work with this either. Any ideas what I'm doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with LOOKUP function
And I think that is the point of HLOOKUP and VLOOKUP but I haven't been able
to construct a formula with them that function correctly. Anyone a H or V LOOKUP Expert that can help? "tim m" wrote: I get the same result as you when testing your data, must have something to do with the highest value being in the left most column as if you make B the higest value it gives the correct result. Has me stumped as well. May have something to do in the sorting of the numerical data. "J" wrote: A1 - "A" B1 - "B" C1 - "C" A2 - 3 B2 - 2 C2 - 1 D2= is returning the MAX in the A2:C2 array and returning 3 E2 is my LOOKUP function. =LOOKUP(D2,A2:C2,A1:C1) Which I thought was saying, look in the array a2:c2, find the value in d2(3), and return the corresponding value in the a1:c1 array, which would be "A". I would be expecting it to return a value of "A" but it's returning "C" instead. I've tried VLOOKUP and HLOOKUP and haven't be able to get that to work with this either. Any ideas what I'm doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with LOOKUP function
The LOOKUP function *requires* the array be sorted ascending.
Try this: =INDEX(A1:C1,MATCH(D2,A2:C2,0)) Or, you could eliminate the separate MAX formula in D2 and incorporate that directly into the "lookup" formula: =INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0)) Biff "J" wrote in message ... A1 - "A" B1 - "B" C1 - "C" A2 - 3 B2 - 2 C2 - 1 D2= is returning the MAX in the A2:C2 array and returning 3 E2 is my LOOKUP function. =LOOKUP(D2,A2:C2,A1:C1) Which I thought was saying, look in the array a2:c2, find the value in d2(3), and return the corresponding value in the a1:c1 array, which would be "A". I would be expecting it to return a value of "A" but it's returning "C" instead. I've tried VLOOKUP and HLOOKUP and haven't be able to get that to work with this either. Any ideas what I'm doing wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with LOOKUP function
That works. Thank you!
"T. Valko" wrote: The LOOKUP function *requires* the array be sorted ascending. Try this: =INDEX(A1:C1,MATCH(D2,A2:C2,0)) Or, you could eliminate the separate MAX formula in D2 and incorporate that directly into the "lookup" formula: =INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0)) Biff "J" wrote in message ... A1 - "A" B1 - "B" C1 - "C" A2 - 3 B2 - 2 C2 - 1 D2= is returning the MAX in the A2:C2 array and returning 3 E2 is my LOOKUP function. =LOOKUP(D2,A2:C2,A1:C1) Which I thought was saying, look in the array a2:c2, find the value in d2(3), and return the corresponding value in the a1:c1 array, which would be "A". I would be expecting it to return a value of "A" but it's returning "C" instead. I've tried VLOOKUP and HLOOKUP and haven't be able to get that to work with this either. Any ideas what I'm doing wrong? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with LOOKUP function
You're welcome. Thanks for the feedback!
Biff "J" wrote in message ... That works. Thank you! "T. Valko" wrote: The LOOKUP function *requires* the array be sorted ascending. Try this: =INDEX(A1:C1,MATCH(D2,A2:C2,0)) Or, you could eliminate the separate MAX formula in D2 and incorporate that directly into the "lookup" formula: =INDEX(A1:C1,MATCH(MAX(A2:C2),A2:C2,0)) Biff "J" wrote in message ... A1 - "A" B1 - "B" C1 - "C" A2 - 3 B2 - 2 C2 - 1 D2= is returning the MAX in the A2:C2 array and returning 3 E2 is my LOOKUP function. =LOOKUP(D2,A2:C2,A1:C1) Which I thought was saying, look in the array a2:c2, find the value in d2(3), and return the corresponding value in the a1:c1 array, which would be "A". I would be expecting it to return a value of "A" but it's returning "C" instead. I've tried VLOOKUP and HLOOKUP and haven't be able to get that to work with this either. Any ideas what I'm doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP function Problem | Excel Worksheet Functions | |||
LOOKUP function problem? | Excel Worksheet Functions | |||
Lookup function problem (kg) | Excel Worksheet Functions | |||
problem lookup function | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) |