Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query
Hi,
I need to lookup some data, find the largest amount, and then return a value in a different cell. Eg: A1 B1 C1 D1 2007 2006 2005 2004 A5 B5 C5 D5 20 35 19 4 So I have to look up the largest value in A5:D5 and return the corresponding value in the same column in cells A1 to D1. In this case the result would be 2006. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query
Try this:
=INDEX(A1:D1,MATCH(MAX(A5:D5),A5:D5,0)) -- Biff Microsoft Excel MVP "StephenAccountant" wrote in message ... Hi, I need to lookup some data, find the largest amount, and then return a value in a different cell. Eg: A1 B1 C1 D1 2007 2006 2005 2004 A5 B5 C5 D5 20 35 19 4 So I have to look up the largest value in A5:D5 and return the corresponding value in the same column in cells A1 to D1. In this case the result would be 2006. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query
=SUMPRODUCT(--(A5:D5=MAX(A5:D5)),A1:D1)
"StephenAccountant" wrote: Hi, I need to lookup some data, find the largest amount, and then return a value in a different cell. Eg: A1 B1 C1 D1 2007 2006 2005 2004 A5 B5 C5 D5 20 35 19 4 So I have to look up the largest value in A5:D5 and return the corresponding value in the same column in cells A1 to D1. In this case the result would be 2006. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query
=SUMIF(A5:D5,MAX(A5:D5),A1:D1)
However, if there are duplicate max values then this and SUMPRODUCT will return the incorrect result. -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =SUMPRODUCT(--(A5:D5=MAX(A5:D5)),A1:D1) "StephenAccountant" wrote: Hi, I need to lookup some data, find the largest amount, and then return a value in a different cell. Eg: A1 B1 C1 D1 2007 2006 2005 2004 A5 B5 C5 D5 20 35 19 4 So I have to look up the largest value in A5:D5 and return the corresponding value in the same column in cells A1 to D1. In this case the result would be 2006. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup query | Excel Discussion (Misc queries) | |||
Possible lookup query - XL2003 | Excel Discussion (Misc queries) | |||
Lookup query in excel | Excel Worksheet Functions | |||
Help please! Lookup/Index query. | Excel Discussion (Misc queries) | |||
Matrix Query Part II - lookup value | Excel Worksheet Functions |