Max Date Lookup with Multiple Condition Criteria

[jws]

Hello,

I have two tabs on my excel sheet, one is the summary and the other tab is the data tab. I need to find the most recent date for the "State" and "Store" combination where a sale occurred.

Example:

• PA had a most recent sale on 1/21/13 in Store 20
• NY had a most recent sale on 1/22/13 in Store 30
• etc

** The value of the sale does not matter, just that there is a positive sale amount, that is what I am trying to pull into the summary tab.

Thanks for looking.

 

[Al Chara]

See if this works for you. The solution assumes you have your data in Data!A1:D19 (column headers in row 1), state in column A, store in column B, date in column C, and sales in column D.

Excel 2010

	A	B	C	D
State	Store 20	Store 30	Store 40
PA
NY
DE

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/21/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/22/2013[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A2,IF(Data!$B$2:$B$19=B$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A2,IF(Data!$B$2:$B$19=C$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A2,IF(Data!$B$2:$B$19=D$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A3,IF(Data!$B$2:$B$19=B$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A3,IF(Data!$B$2:$B$19=C$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A3,IF(Data!$B$2:$B$19=D$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A4,IF(Data!$B$2:$B$19=B$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A4,IF(Data!$B$2:$B$19=C$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]{=MAX(IF(Data!$A$2:$A$19=$A4,IF(Data!$B$2:$B$19=D$1,IF(Data!$D$2:$D$19>0,Data!$C$2:$C$19))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[jws]

I have been trying to work on this, but my results are not as expected. I am going to continue to try other solutions

thanks

 

[Al Chara]

The above solution should work if you have the data set up as earlier described. If the data is set up a bit differently, then the formulas might need some minor tweaking. If you post a sample of your data tab and summary tab information (please do not post any sensitive information), then I can help make the necessary modifications.

 

[sabinezp]

The above solution should work if you have the data set up as earlier described. If the data is set up a bit differently, then the formulas might need some minor tweaking. If you post a sample of your data tab and summary tab information (please do not post any sensitive information), then I can help make the necessary modifications.

this solution worked perfectly for me, THANK YOU .