# How to find the latest date in a table with given parameters

#### YukonBrad

##### New Member
Hi Again,

I'm back, stumped again, hoping for some help.

I have a spreadsheet where i track injuries and losses and the table has the the following columns

DATE LOCATION LOSS (Injury, Environmental, Vehicle) //others//

The data goes back for about three years and what I would like to be able to do is write a formula that would tell me the latest date for each location for each loss type.

For instance, the DATE of the last INJURY that LOCATION "A" had = [then the formula that i can't figure out]

Hope this makes sense. Thanks for your help.

--Brad

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Assuming
Date Col A
Location Col B
H1 is location for which you're looking up last date
I1 =MAX(IF(B:B=H1,A:A)) ARRAY formula after typing formula press Ctrl+Shift+Enter instead of pressing "Enter". {} will automatically be placed around formula - don't type {} .

HI rallcorn,
That worked but i need a little more, i need to know the location and LOSS type. Your formula gave me the latest date of loss for the location in question but I also was hoping to see the latest date for injury by location.

So using your assumptions

Date Col A
Location Col B
Loss type Col C
H1 would be location
L1 would be loss type

Date of last L1 in H1 =. . . ideas?

Thanks again.
--Brad

For instance, the DATE of the last INJURY that LOCATION "A" had
The solution I provided answered this request.

I also was hoping to see the latest date for injury by location

I'm confused!

Do you want to see all Locations with a list of loss types for that have occurred for each location along with the date of the most recent time each loss occurred at that location?

or
Do you want to see all Loss Types with a list of the locations at which they have occurred along with the most recent date the loss type occurred at each locaion?

or
Do you want to be able to pick ONE location with a list of loss types for that have occurred for at that location along with the date of the most recent time each loss occurred at that location?

I'd probably approach any of the above with a pivot table.
Place Location and Loss Type in the Row Labels section (in whichever order you need).

Place DATE in the Values section (change the Value Field Setting to MAX and Number Format to Date).

This shows by Location each loss type and most recent occurrence
 Max of Date Location Loss Type Total A Environmental 3/1/2011 Injury 3/2/2011 Other 3/4/2011 Vehicle 3/3/2011 B Environmental 4/1/2011 Injury 4/2/2011 Other 4/4/2011 Vehicle 4/3/2011 C Environmental 5/1/2011 Injury 5/2/2011 Other 5/4/2011 Vehicle 5/3/2011

<TBODY>
</TBODY>

This shows by Loss Type the most recent occurrence at each location where the loss type has happened.
 Max of Date Loss Type Location Total Environmental A 3/1/2011 B 4/1/2011 C 5/1/2011 Injury A 3/2/2011 B 4/2/2011 C 5/2/2011 Other A 3/4/2011 B 4/4/2011 C 5/4/2011 Vehicle A 3/3/2011 B 4/3/2011 C 5/3/2011

<TBODY>
</TBODY>

If you want to show just a single location, drag Location from Row Labels section to Report Filter section and select the single Location you wish to see:

 Location A Max of Date Loss Type Total Environmental 3/1/2011 Injury 3/2/2011 Other 3/4/2011 Vehicle 3/3/2011

<TBODY>
</TBODY>

Maybe this will get you headed in the right direction. PivotTable will be much quicker and easier than a formula solution.

Replies
15
Views
374
Replies
1
Views
158
Replies
2
Views
224
Replies
16
Views
388
Replies
1
Views
160

Threads
1,202,987
Messages
6,052,932
Members
444,616
Latest member
novit19089

### 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

### Disable AdBlock

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