Find Cell valiue based on date values

jonybandana

New Member
Joined
Dec 16, 2022
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. I have the following table:
Forum tests.xlsx
ABCD
1ProjectStepOwnerDate
221InitiationJohn1/2/2023
321PlanningJim7/2/2023
421ExcecutionLisa8/2/2023
521ClosingMark11/2/2023
634InitiationJohn12/2/2023
734PlanningJim13/2/2023
834ExcecutionLisa14/2/2023
934ClosingMark15/2/2023
1054InitiationJohn16/2/2023
1154PlanningJim17/2/2023
1254ExcecutionLisa18/2/2023
1354ClosingMark19/2/2023
1467InitiationJohn20/2/2023
1567PlanningJim21/2/2023
1667ExcecutionLisa22/2/2023
1767ClosingMark23/2/2023
Steps


And in another sheet:
Forum tests.xlsx
ABCD
1ProjectStatusFollowupOwner
221Ongoing11/2/2023
334Ongoing15/2/2023
454Ongoing19/2/2023
567Ongoing23/2/2023
Summary
Cell Formulas
RangeFormula
C2:C5C2=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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
How about
Excel Formula:
=FILTER(Table1[Owner],(Table1[Project]=[@Project])*(Table1[Date]=[@Followup]))
 
Upvote 1
Solution
How about
Excel Formula:
=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?
 
Upvote 0
Check that the table & column names are correct.
 
Upvote 0
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.
 
Upvote 0
You can get rid of the #spill error like
Excel Formula:
=index(FILTER(Table1[Owner],(Table1[Project]=[@Project])*(Table1[Date]=[@Followup])),1)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top