Update cell value based on dates in other columns

Question

I would like to:

1. Update a cell (A2) with a status. That could be a standardised list, for example: "Sent to Customer", "Customer Replied", "Task Closed".
2. There are three columns named - Sent to Customer (B1), Customer Replied (C1), Tasked Closed(D1). In those columns dates are entered once that stage is completed. For example: B2 = 01/01/2023, C2 = 02/01/2023, D2= 03/01/2023. In this example the cell in A2 should read "Task Closed".
3. The situation can arise where the most recent data could be in C3 - Customer Replied. In those instances A3 would need to read Customer Replied. The formula needs to find the earliest date in the columns B, C or D.

For Example

I've tried compiling the formula into a single cell for the data in rows 2 and 3. The compiled formula for row 2 is returning the wrong status? Why is that, what am I missing?

Answer 1

Your formulas don't match themselves.

ROW 2   
Offset      =OFFSET(B1,...   
Compiled    =OFFSET(A1,...

ROW 3   
Offset      =OFFSET(B1,...   
Compiled    =OFFSET(B1,...

Where you offset from has to line up with the range you search within the MATCH() function. I think you just have a typo. If the compiled formula for row 2 started with =OFFSET(B1, then I expect it would work fine.

---

Side note, you may want to throw at least one $ in there to lock down the absolute referencesso you can copy / paste the formula down. I would use this as the compiled formula in row 2:

=OFFSET(B$1,,MATCH(MAX(C2:E2),C2:E2,0))

Alternatively:

=INDEX(C$1:E$1,MATCH(MAX(C2:E2),C2:E2,0))