Max Date

[jswillis07]

I have two worksheets that I am using. Sheet A contains raw data including a key number and a corresponding date. Sheet B just contains a key number. I am trying to find the latest date in a set of values if the key number from Sheet B matches the key # from Sheet A.
Example:
Sheet A Sheet B
Key # Date Key # Latest Date
1 11/10 1 11/12
1 11/11 2 12/1
1 11/12 3 12/4
2 12/1
3 12/3
3 12/4

I have tried using a combination of max vlookups however vlookup merely returns the first date value and doesn’t allow the max to be found.

I would greatly appreciate any assistance.

Thank you

 

[Jonmo1]

Are the key numbers sorted in ascending order in Sheet A ?

If so, try
=LOOKUP(A2,'Sheet A'!A$2:B$7)

 

[Fowmy]

Hi,

Welcome to the forum,

Enter following array formula by pressing Ctrl+Shift+Enter


=MAX(IF(Sheet1!A$2:A$32=Sheet2!A2,Sheet1!B2:B32))

 

[jswillis07]

Jonmo1: My example was a little misleading. My actual data is not in ascending order.
Fowmy: The formula makes sense and seems as if it would work, however I am still not getting the correct date. I will continue to play around with it.

I thank both of you for your help.

 

[Jonmo1]

Fowmy's formula should work fine.
You would need to lock the Row#s on both ranges though if you're filling it down..

=MAX(IF(Sheet1!A$2:A$32=Sheet2!A2,Sheet1!B$2:B$32))

Also, Make sure you're entering the formula with CTRL + SHIFT + ENTER
When entered correctly it will be enclosed in {brackets}

 

[Fowmy]

It will be helpful if you could share with us the date you got and the implemented formula, also show a screenshot of your data from sheet A.

Make sure you replaced the correct sheet names in the formula.

 

[Aladin Akyurek]

Sheet B

B2, just enter and copy down:

=AGGREGATE(14,6,'Sheet A'!$B$2:$B$7/(('Sheet A'!$A$2:$A$7=$A2)),1)