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.

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.

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.