EXCEL FORMULA

Kunal_Mehta 

Using 365 (also required for the FILTER solution):

= LET(
    name, SORT(UNIQUE(Table1[Name])),
    HSTACK(name, MAP(name, Classifyλ))
  )

where the Lambda function, Classifyλ, is defined by

= XLOOKUP(
    100000, 
    IF(Table1[Name] = n, Table1[Date]), 
    Table1[Classification], ,
    -1
  )

To further conceal the actual formula one could wrap the formula within a further Lambda so that the worksheet formula becomes

= CurrentClassificationsλ()

The main criticism of that formula is that it has Table1 hard-wired into the formula rather than showing it as a predecessor using a parameter.   Passing the parameter into the Classifyλ function and providing an alternative to the structured referencing is more challenging. 

Kunal_Mehta 

As an alternative, if I may add...

Here’s an example formula that you can use in the “Cloud Classification” column in Sheet1:

=INDEX(Sheet2!C:C,MATCH(1,(Sheet2!A:A=A2)*(Sheet2!B:B=MAX(IF(Sheet2!A:A=A2,Sheet2!B:B))),0))

This is an array formula, so you need to enter it by pressing Ctrl+Shift+Enter instead of just Enter.

This formula assumes that the “Name” column is in column A and the “Date” column is in column B in both sheets, and the “Cloud Classification” column is in column C in Sheet2.

You can adjust the column references as needed.

This formula will find the row in Sheet2 where the “Name” matches the current row in Sheet1 and the “Date” is the latest for that “Name”.

It will then return the value from the “Cloud Classification” column in that row.

I hope this helps!

Hi Kunal_Mehta 

In B2

=FILTER(Sheet2!$J$3:$J$9,(Sheet2!$I$3:$I$9=MAXIFS(Sheet2!$I$3:$I$9,Sheet2!$H$3:$H$9,Sheet1!A2))*(Sheet2!$H$3:$H$9=Sheet1!A2))

Lorenzo 

Hi, don't you think that using XLOOKUP will not necessarily return the value corresponding to the name but may be another value whose name has the same date as that found by MAXIFS ?

Hi Kunal_Mehta 

Could you revise the title of this thread so it better reflects the challenge please (this will help people who Search this site) - Thanks

in A2

=SORT(UNIQUE(D2:D9))

in B2

=XLOOKUP(MAXIFS(E2:E9,D2:D9,A2#),E2:E9,F2:F9)