Q:

Return status of customer from most recent contact date

I have a list of customers, and a spreadsheet that tracks each encounter with them.      A sample table looks like this:
Workbook 1: Table of customers
+------------+----------------+---------------------+
|    Name    | Current Status | Last Date Contacted |
+------------+----------------+---------------------+
| Customer A | Active         | 3/1/2018            |
| Customer B | Inactive       | 3/2/2018            |
| Customer C | Closed         | 3/3/2018            |
+------------+----------------+---------------------+

Workbook 2: List of encounters
+------------+------------+----------+
|    Name    |   Status   |   Date   |
+------------+------------+----------+
| Customer A | New        | 1/1/2018 |
| Customer A | Active     | 2/1/2018 |
| Customer A | Active     | 3/1/2018 |
| Customer B | New        | 1/2/2018 |  
| Customer B | Active     | 2/2/2018 |
| Customer B | Disengaged | 3/2/2018 |
| Customer C | New        | 1/3/2018 |
| Customer C | Active     | 2/3/2018 |
| Customer C | Closed     | 3/3/2018 |
+------------+------------+----------+

I have the following formula to return the last date contacted:
{=MAX(IF(Encounters[Name] = [@[Last Then First Name]], Encounters[Date])))}
I would like a formula that looks for the most recent date for each Customer, and return the status associated with that date.  Is there a way to do this with formulas.  Keep in mind that the formula would need to continue to work even if the worksheet were to be sorted.  Thanks!

A:

Assuming your table of customers is in Sheet1 (columns A to C) and your list of encounters is in Sheet2 (column A to C), and you already have your latest date of encounter per customer in column C of Sheet1, then you can enter the following formula in Sheet 1 cell B2 (then drag down to your last customer):
=INDEX(Sheet2!$B:$B,MATCH(1,(Sheet1!$A2=Sheet2!$A:$A)*(Sheet1!$C2=Sheet2!$C:$C),0))

This allows you to capture the latest status per customer. The formula would however be pretty taxing on the efficiency of Excel's calculation, so you can change the reference ranges to your structured table references. Please don't forget to return the formula using CONTROL+SHIFT+ENTER.

