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
1	State	Store 20	Store 30	Store 40
2	PA	1/21/2013
3	NY		1/22/2013
4	DE			1/22/2013
5

<tbody>
</tbody>

Summary​

Array Formulas Cell Formula B2 {=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))))} C2 {=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))))} D2 {=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))))} B3 {=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))))} C3 {=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))))} D3 {=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))))} B4 {=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))))} C4 {=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))))} D4 {=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))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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 .