Find Cell valiue based on date values

[jonybandana]

Hi everyone. I have the following table:

Forum tests.xlsx
	A	B	C	D
1	Project	Step	Owner	Date
2	21	Initiation	John	1/2/2023
3	21	Planning	Jim	7/2/2023
4	21	Excecution	Lisa	8/2/2023
5	21	Closing	Mark	11/2/2023
6	34	Initiation	John	12/2/2023
7	34	Planning	Jim	13/2/2023
8	34	Excecution	Lisa	14/2/2023
9	34	Closing	Mark	15/2/2023
10	54	Initiation	John	16/2/2023
11	54	Planning	Jim	17/2/2023
12	54	Excecution	Lisa	18/2/2023
13	54	Closing	Mark	19/2/2023
14	67	Initiation	John	20/2/2023
15	67	Planning	Jim	21/2/2023
16	67	Excecution	Lisa	22/2/2023
17	67	Closing	Mark	23/2/2023
Steps

And in another sheet:

Forum tests.xlsx
	A	B	C	D
1	Project	Status	Followup	Owner
2	21	Ongoing	11/2/2023
3	34	Ongoing	15/2/2023
4	54	Ongoing	19/2/2023
5	67	Ongoing	23/2/2023
Summary

Cell Formulas
Range	Formula
C2:C5	C2	=MAXIFS(Table1[Date],Table1[Project],[@Project])

I would like to have in the "Summary" sheet, where it says "Owner", the person assigned to the step that has the date in the "followup" column.

How could I do this?

 

[Fluff]

How about

=FILTER(Table1[Owner],(Table1[Project]=[@Project])*(Table1[Date]=[@Followup]))

 

[jonybandana]

How about

=FILTER(Table1[Owner],(Table1[Project]=[@Project])*(Table1[Date]=[@Followup]))

I´m getting #REF! I think because in the original file, in Table 1 "Date" not all cells have data. How could I avoid this?

 

[Fluff]

Check that the table & column names are correct.

 

[jonybandana]

Check that the table & column names are correct.

There where some #REF errors in the Date column. I solved those and that was the end of it. The formula works great.

I am getting a SPILL error sometimes, because sometimes one or more steps are assigned to the same person, but I solved it by entering the formular with CTRL+SHIFT+ENTER. I´m not sure if this is the right way to go, but For now it works ok.

 

[Fluff]

You can get rid of the #spill error like

=index(FILTER(Table1[Owner],(Table1[Project]=[@Project])*(Table1[Date]=[@Followup])),1)