Using index match but there are multiple matches, i want the most recent

[simongood]

Sorry posted this in general forum by mistake.

Hi, i'm not sure if I can do what i'm trying to do with an index match formula. My index match formula 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 alot,
Simon

[alansidman]

Look at this link. I think Debra explains far better than I could and she give clear examples.

http://blog.contextures.com/archives...dex-and-match/

[sktneer]

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter)

Please Login or Register  to view this content.

In the above formula, change the range reference as per your requirement as referencing whole column is not a good idea. So say if your data is in the range A2:C100 with row 1 as the header row and on sheet2 A1 contains the Email, then on sheet2 in B1, you may try something like this......

Please Login or Register  to view this content.

Does this help?

[simongood]

Thanks all for your help but we haven't solved it yet.

@alansidman, thanks for the link but what Debra goes through isn't quite what i'm after. Debra gives a solution to find an answer based on multiple criteria. I'm looking for matches based on a single criterion, and I want excel to return the latest date given the match function can return multiple answers that match the criterion and are therefore correct.

sktneer, thanks for your suggestion but it didn't work for me. I'll be more specific about my spreadsheet and the exact columns and formulas i used, maybe you can see where i went wrong.

Worksheet called 'data' has 10001 rows. Row 1 are the headers.

Relevant columns are:
Column D - ID (i'm using this instead of email which i referred to in the original post)
Column H - this is the column that has the date that I want to be returned in the other worksheet.

Worksheet # 2 is called working.
Column F has the ID that i'm trying to match up with in Column D in the 'data' worsheet. There could be 0 matches, there could be 1 match, there could be 10 matches. If there's more than 1 match i want to latest date found in column 8 that matches the ID, returned.
Column I is where i want my answer

Based on your formula i have this in I2:

=INDEX(Data!$H$2:$H$10001,MAX(IF(Data!$D$2:$D$10001=working!F2,ROW(Data!$D$2:$D$10001)-ROW(Data!$D$2)+1)))

and i did ctrl-shift-enter. Can you see where i'm going wrong?

thanks
Simon

[sktneer]

May be this......

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter. When an array formula is confirmed correctly, you will notice in the formula bar that the formula is surrounded by curly braces. If you don't see the curly braces in the formula bar, select the cell which contains the array formula, hit F2 (function key) and try again to confirm it with Ctrl+Shift+Enter.

[simongood]

Thanks stkneer. That works! I got a similar answer from another forum (except without the IF statement for the 'not found' part).

cheers
S

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.