Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Max of a related Value Lookup
Hi Folks,
I have been struggling with this for a bit now. I have items available for purchase in E8:e66 (using list validation). I have item written in $r8. I have the actual cost paid for the item for that particular purchase in h8:h66. an Item can be purchased multiple times at different prices. What I am trying to do is create a formula that will lookup all amounts paid for the specific item type and return the maximum cost paid for that item. My current formula version only reports back the first one found rather than the maximum of all types of that item purchased. =IF(W9=0,"None",MAX(VLOOKUP($R9,$E$8:$H$66,4,FALSE ))) where w9=amount of units purchased.(error removal) Its really buggung me because its a simple result but a pain to generate. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Max of a related Value Lookup
One way ..
Put in say, the formula bar for X9, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(W9=0,"None",MAX(IF($E$8:$E$66=R9,$H$8:$H$66))) X9 returns the max from col H for the item in R9 Copy X9 down Adapt the ranges to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cav" wrote in message ... Hi Folks, I have been struggling with this for a bit now. I have items available for purchase in E8:e66 (using list validation). I have item written in $r8. I have the actual cost paid for the item for that particular purchase in h8:h66. an Item can be purchased multiple times at different prices. What I am trying to do is create a formula that will lookup all amounts paid for the specific item type and return the maximum cost paid for that item. My current formula version only reports back the first one found rather than the maximum of all types of that item purchased. =IF(W9=0,"None",MAX(VLOOKUP($R9,$E$8:$H$66,4,FALSE ))) where w9=amount of units purchased.(error removal) Its really buggung me because its a simple result but a pain to generate. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Max of a related Value Lookup
Cheers did the trick nicely.
Thanks "Max" wrote: One way .. Put in say, the formula bar for X9, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(W9=0,"None",MAX(IF($E$8:$E$66=R9,$H$8:$H$66))) X9 returns the max from col H for the item in R9 Copy X9 down Adapt the ranges to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cav" wrote in message ... Hi Folks, I have been struggling with this for a bit now. I have items available for purchase in E8:e66 (using list validation). I have item written in $r8. I have the actual cost paid for the item for that particular purchase in h8:h66. an Item can be purchased multiple times at different prices. What I am trying to do is create a formula that will lookup all amounts paid for the specific item type and return the maximum cost paid for that item. My current formula version only reports back the first one found rather than the maximum of all types of that item purchased. =IF(W9=0,"None",MAX(VLOOKUP($R9,$E$8:$H$66,4,FALSE ))) where w9=amount of units purchased.(error removal) Its really buggung me because its a simple result but a pain to generate. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Max of a related Value Lookup
Glad it worked !
Thanks for the feedback .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cav" wrote in message ... Cheers did the trick nicely. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the Max of a related Value Lookup
Hi,
Assume your sheet is set up like this in range A1:B7. Ashish 100 Sanjay 200 Pongal 300 Ashish 400 Rajesh 500 Suresh 600 Ashish 700 Ashish (in cell A10) In cell B10, type the following array formula (Ctrl+shift+Enter) =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2)) Copy this formula down. you will now get all values which appear against Ashish. Now you can use the max function in a seperate cell. Please suit the example to your needs. Regards, "Cav" wrote: Hi Folks, I have been struggling with this for a bit now. I have items available for purchase in E8:e66 (using list validation). I have item written in $r8. I have the actual cost paid for the item for that particular purchase in h8:h66. an Item can be purchased multiple times at different prices. What I am trying to do is create a formula that will lookup all amounts paid for the specific item type and return the maximum cost paid for that item. My current formula version only reports back the first one found rather than the maximum of all types of that item purchased. =IF(W9=0,"None",MAX(VLOOKUP($R9,$E$8:$H$66,4,FALSE ))) where w9=amount of units purchased.(error removal) Its really buggung me because its a simple result but a pain to generate. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup finding the next value that is GREATER | Excel Worksheet Functions | |||
vlookup - finding the next value that is GREATER than the lookup value? | Excel Worksheet Functions | |||
Finding LARGE value within range of lookup table | Excel Discussion (Misc queries) | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |