Returning the most recent date when INDEX MATCH has multiple matches

1
votes

I have an index match formula which goes through a list and returns a date based on an email address. My issue I have is what if there are multiple entries that match that email address? How do I return the most recent date?

e.g. worksheet 1 has all the data

  A            B            C                 
email         ID            date

worksheet 2 has a list of specific email addresses i'm looking for info on

A         B
email   date

My formula in column B of worksheet 2 is along the lines of this:

index(worksheet1 C:C,match(worksheet2 A1, worksheet 1 A:A,false))

Basically saying where you find the email address in A1 listed in column A of worksheet 1, return in B1 the value in the column of that row in worksheet1.

The thing is we could have the same email address listed a number of times, so I'm looking for the latest date to be returned, not the first one it finds.

Thanks simon

1
excelexcel-formula

1 Answers

5
votes

You can try using DMAX Function
=DMAX(Sheet1!A1:C5,3,A1:A2)

Worksheet1:
Worksheet1

Worksheet2:
enter image description here

Here's more on DMAX Function.
If however you have a lot of emails in sheet 2 that you want to look-up, another approach is needed.

If that is the case, you can try this Array Formula.
=MAX(IF(Sheet1!$A$1:A$5=A3,Sheet1!$C$1:$C$5))
Above formula is entered by Ctrl+Shift+Enter in Cell B2 as shown below.

Result:
enter image description here

As you can see, it has the same result as the DMAX.
But you can copy and paste or auto-fill this formula to succeeding cells in B.