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

  • Jgomez
    Jgomez ✭✭✭
    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
    Jgomez ✭✭✭
    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!

Answers

  • Jgomez
    Jgomez ✭✭✭
    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
    Jgomez ✭✭✭
    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!

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!