Question

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.

Jgomez · Accepted Answer

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)

Jgomez · Accepted Answer

***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!