Index and match based on max date

[Alexvs]

Hi, I'm hoping someone can help as I'm racking my brain to get the answer I'm sure is available.

I've set up a spreadsheet for one of my account teams that they can use to input customer lists our clients send us and find the current statuses of that customer's account, in this case there are 2 statuses per account. What I've done so far is use an Index and Match formula to display these statuses in the table on sheet 1 based on the customer ID being matched in our customer database copied into sheet 2. This is all well and good except the database they copy into sheet 2 has every account update per customer so it may list 3 or 4 statuses per customer by date updated.

What I need to be able to do is display exactly what I have in place except the statuses need to be the last updated status based on the date in column AE on sheet 2 where currently it is obviously bringing the first status.

I've added a sample of random data but kept the information in the columns that they're in on my actual workbook but I've had to delete everything else out due to it being confidential data and there's far too much in the database to try add random data for all columns.

Once in place I need to be able to hand this workbook to the account manager and let them copy in the latest customer list received into sheet1 and latest database into sheet2 and have the 2 status columns update.

Is this possible?

Thanks

Alex

[JBeaucaire]

Without adding any helper columns to your data to reduce calculation time, this will do it... but a LOT of these formulas and/or setting the search range on sheet2 to too many rows might sincerely slow down performance.

In C2:

=INDEX(Sheet2!$AC$1:$AC$2000, LOOKUP(2, 1/(Sheet2!$D$1:$D$2000=$B2),ROW($A$1:$A$2000)))

In D2:

=INDEX(Sheet2!$AD$1:$AD$2000, LOOKUP(2, 1/(Sheet2!$D$1:$D$2000=$B2),ROW($A$1:$A$2000)))

[Alexvs]

Thanks so much for that, I copied it over to my working sheet and all is good. I know what you mean about the search range, I've reduced it slightly but our current customer database is obviously continuously growing so I'll just monitor this on an ongoing basis to adjust formulae as we grow.

Thanks again.