Calculate duration based on matched value of 2 columns

Hi there, long time reader first time writer....
I'm trying to figure out a way to calculate the duration from 1 date in column 4 to another date in column 4 based on matching/same names in column 2. To help explain the workflow from my head - Column 4 contains a date, column 2 contains a name, and column 3 contains an event (ex. Planning, or In progress, or Completed). Due to spelling differences based on who entered the data, that could vary (ex. Planning, or planning, or plnning, or planning 1, or planning 2 etc.). What I need to do is - if column 2 matches the name in "this cell", and column 3 says "Completed" OR "Completed2" OR" Complted", then calculate the date difference from "this row" to the row which had the same name AND the word "Completed".
I attempted using a nested VLOOKUP and IF statement but it obviously couldn't work since VLOOKUP stops once it finds a match. I'm not super practiced in Excel/SmartSheet but I know I could maybe use Index/Match but I'm not sure how to use that properly in this case. Any help would be AhMazing.
Below is a sample - I blocked the names but this is what I'm working off of. Under type of event we have study date, or exam date, or exam 2, etc.
Best Answers
-
UPDATE* I tried using Index Collect after some more hunting, but that's not working out for me either. See below:
=INDEX(COLLECT([Date of Event]:[Date of Event], [Tech name (First Last)]:[Tech name (First Last)], [Tech name (First Last)]@row, [Type of Event]:[Type of Event], "Exam"), 1)
-
***UPDATE : I figured out the issue after some more searching, this formula actually worked perfectly but I had to change the column in question (Invalid Column Value) to a Date Format since the code was pulling that "Date of Event". Once I did that this was flawless!
Answers
-
UPDATE* I tried using Index Collect after some more hunting, but that's not working out for me either. See below:
=INDEX(COLLECT([Date of Event]:[Date of Event], [Tech name (First Last)]:[Tech name (First Last)], [Tech name (First Last)]@row, [Type of Event]:[Type of Event], "Exam"), 1)
-
***UPDATE : I figured out the issue after some more searching, this formula actually worked perfectly but I had to change the column in question (Invalid Column Value) to a Date Format since the code was pulling that "Date of Event". Once I did that this was flawless!
-
Hi @Jgomez
Thank you for following-up and posting your solution! I'm glad you were able to get this to work.
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!