# Formula Needed to Display Most Recent Date Entered

#### Carrie Kaelin

##### New Member
I need a way to extract the most recent accident date entered for a particular store. Example:

Store 1 12/02/03
Store 1 10/24/04
Store 1 01/01/05
Store 2 01/05/05
Store 2 10/31/05

In the above example, I would need the most recent date of 01/01/05 to be displayed for Store 1 and 10/31/05 for Store 2. Is there also a way if there are no accidents for a particular store that the cell will stay blank next to the store #?

Thank you for your assistance. -Carrie

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Welcome

try:
=IF(MAX((A1:A5="Store 1")*B1:B5)=0,"",MAX((A1:A5="Store 1")*B1:B5))

Enter using Ctrl-Shift-Enter.

Carrie Kaelin said:
I need a way to extract the most recent accident date entered for a particular store. Example:

Store 1 12/02/03
Store 1 10/24/04
Store 1 01/01/05
Store 2 01/05/05
Store 2 10/31/05

In the above example, I would need the most recent date of 01/01/05 to be displayed for Store 1 and 10/31/05 for Store 2. Is there also a way if there are no accidents for a particular store that the cell will stay blank next to the store #?

Thank you for your assistance. -Carrie
Hi Carrie:

Welcome to MrExcel Board!

If I understand you correctly you can use the following array formula in cell E2 ...

=Max(IF(\$A\$2:\$A\$6=D2,\$B\$2:\$B\$6))

this is then copied down

as illustrated in the following ...
Book1
ABCDE
1StoreDate
2Store112/02/2003Store101/01/2005
3Store110/24/2004Store210/31/2005
4Store101/01/2005
5Store201/05/2005
6Store210/31/2005
7
Sheet1

Yogi:

Your formula worked great, but I thought of one more additional question. Can I add a condition to the if statement? If a store does not have any accidents it displays 01/0/1900 next to the store name. Can a condition be added to the MaxIf formula to enter the text "No Accidents" next to the store name.

My data is on a different worksheet. I modified the formula you provided to the following: =MAX(IF(Data!\$A\$1:\$A\$6000=A2,Data!\$D\$1:\$D\$6000)) where where A2 lists the store name on my current worksheet. If the store does not appear on our accident listing, I wanted "No Accident" listed beside the name. I attempted the following =MAX(IF(Data!\$A\$1:\$A\$6000=A2,Data!\$D\$1:\$D\$6000, "No Accident")) but it does not work.

Thanks again for any suggestions, Carrie

Yogi Anand said:
Carrie Kaelin said:
I need a way to extract the most recent accident date entered for a particular store. Example:

Store 1 12/02/03
Store 1 10/24/04
Store 1 01/01/05
Store 2 01/05/05
Store 2 10/31/05

In the above example, I would need the most recent date of 01/01/05 to be displayed for Store 1 and 10/31/05 for Store 2. Is there also a way if there are no accidents for a particular store that the cell will stay blank next to the store #?

Thank you for your assistance. -Carrie
Hi Carrie:

Welcome to MrExcel Board!

If I understand you correctly you can use the following array formula in cell E2 ...

=Max(IF(\$A\$2:\$A\$6=D2,\$B\$2:\$B\$6))

this is then copied down

as illustrated in the following ...
Book1
ABCDE
1StoreDate
2Store112/02/2003Store101/01/2005
3Store110/24/2004Store210/31/2005
4Store101/01/2005
5Store201/05/2005
6Store210/31/2005
7
Sheet1

Hi Carrie:

Try the following array formula ...

=IF(ISNA(MATCH(A2,data!\$A\$1:\$A\$6000,0)),"No Accident",MAX(IF(data!\$A\$1:\$A\$6000=A2,data!\$D\$1:\$D\$6000)))

Everything works perfect. Many thanks, Carrie

You Are Very Welcome Carrie ... Now Let Us Keep EXCELing!

Replies
1
Views
129
Replies
4
Views
337
Replies
1
Views
195
Replies
13
Views
471
Replies
13
Views
213

1,196,515
Messages
6,015,661
Members
441,914
Latest member
VBAllTheThings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back