Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup on multiple criteria and max
Using the example below, I need to match criteria in column a (Item #),
column B (store #) and where column D (Update Date) has the highest value, to return the data from column C (New Status). Item # Store # New Status Update Date 17829 4 A 8/9/2006 17829 4 D 4/29/2004 17829 4 N 3/15/2004 17829 4 I 1/26/2004 33467 33 A 8/9/2006 33467 33 D 4/29/2004 55000 53 D 4/29/2004 55000 53 N 3/15/2004 For instance, for store 4, item #17829, i would need new status A to be the resullt. For store 33, item # 33467, I would need status A to be the result. For store 53, item # 55000, I would need status D to be the result. Any and all help is greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup on multiple criteria and max
=MAX(IF(($A$2:$A$9=a10)*($B$2:$B$9=B10),D2:D9))
Array entered (Ctrl+Shift+Enter) assuming your crteria for Store # is in cell b10 and Item # in A10 "jeffbert" wrote: Using the example below, I need to match criteria in column a (Item #), column B (store #) and where column D (Update Date) has the highest value, to return the data from column C (New Status). Item # Store # New Status Update Date 17829 4 A 8/9/2006 17829 4 D 4/29/2004 17829 4 N 3/15/2004 17829 4 I 1/26/2004 33467 33 A 8/9/2006 33467 33 D 4/29/2004 55000 53 D 4/29/2004 55000 53 N 3/15/2004 For instance, for store 4, item #17829, i would need new status A to be the resullt. For store 33, item # 33467, I would need status A to be the result. For store 53, item # 55000, I would need status D to be the result. Any and all help is greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup on multiple criteria and max
Try this:
=INDEX(C5:C12,MATCH(1,(A5:A12=55000)*(B5:B12=53),0 )) ctrl shift enter (not just enter) "jeffbert" wrote: Using the example below, I need to match criteria in column a (Item #), column B (store #) and where column D (Update Date) has the highest value, to return the data from column C (New Status). Item # Store # New Status Update Date 17829 4 A 8/9/2006 17829 4 D 4/29/2004 17829 4 N 3/15/2004 17829 4 I 1/26/2004 33467 33 A 8/9/2006 33467 33 D 4/29/2004 55000 53 D 4/29/2004 55000 53 N 3/15/2004 For instance, for store 4, item #17829, i would need new status A to be the resullt. For store 33, item # 33467, I would need status A to be the result. For store 53, item # 55000, I would need status D to be the result. Any and all help is greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|