Formula to search max. value based on criteria in other column

[roelandwatteeuw]

Hi all

I have this data with columns:
- A: the ID of the product
- B: the start of the proces
- C: the end of te proces
- D: the state of the proces during B to C

ID	Start	End	State
10028700​	1/01/2020​	31/03/2021​	Active
10028700​	1/04/2021​	31/12/9999​	Inactive
10029917​	1/07/2021​	30/09/2021​	Active
10029917​	1/10/2021​	31/12/9999​	Active
10030325​	1/10/2019​	2/03/2021​	Active
10030325​	1/04/2022​	31/12/9999​	Inactive
10031873​	1/09/2021​	3/10/2021​	Inactive
10031873​	4/10/2021​	14/11/2021​	Active
10031873​	15/11/2021​	31/01/2022​	Active
10031873	01/02/2022​	31/12/9999​	Active

What do I need:

A formula where Excel first looks at all the same ID's (A).
For those ID's I need the State (D) of the last End date (C) within all the rows with this ID.

This what it should look like:

ID	Start	End	State	Last state
10028700​	1/01/2020​	31/03/2021​	Active	Inactive
10028700​	1/04/2021​	31/12/9999​	Inactive	Inactive
10029917​	1/07/2021​	30/09/2021​	Inactive	Active
10029917​	1/10/2021​	31/12/9999​	Active	Active
10030325​	1/04/2022​	31/12/2025​	Active	Active
10030325​	1/10/2019​	31/03/2022​	Inactive	Active
10031873​	1/09/2021​	3/10/2021​	Inactive	Active
10031873​	4/10/2021​	14/11/2021​	Active	Active
10031873​	15/11/2021​	31/01/2022​	Active	Active
10031873	01/02/2022​	31/12/9999​	Active	Active

Hope you can help me!


gr.
Roely

 

[roelandwatteeuw]

And found a solution for the dynamic range:

=INDEX(SORT(FILTER($B$2:    INDIRECT(CONCATENATE("D",$L$1))     ,$A$2:   INDIRECT(CONCATENATE("A",$L$1))   =A2),1,-1),1,3)

And L1 =

=match(2,1/(D:D<>""),1)

 

[Fluff]

That is volatile & will calculate if you so much as breathe.
You would be better off with something like

=LET(rw,LOOKUP(2,1/(A:A<>""),ROW(A:A)),INDEX(SORT(FILTER(INDEX(B:B,2):INDEX($D:$D,rw),$A$2:INDEX($A:$A,rw)=A2),1,-1),1,3))

 

[roelandwatteeuw]

That is volatile & will calculate if you so much as breathe.
You would be better off with something like

=LET(rw,LOOKUP(2,1/(A:A<>""),ROW(A:A)),INDEX(SORT(FILTER(INDEX(B:B,2):INDEX($D:$D,rw),$A$2:INDEX($A:$A,rw)=A2),1,-1),1,3))

Works great as well. ??
Didn't know the 'LET-function'. Definitely will be handy in the Future!

 

[Fluff]

My pleasure.