Nested IF statement with VLookup Formula

Hello,

I have two sheets that contain data from employee responses to different questions. Employees answer at least one question per day.

If an employee answers the question on sheet 1, I need that value, if they dont have a response on that sheet, I need to look and see if there is an answer is on a different sheet. I only want responses for 'Today'. I have a cell in the lookup sheet that has =Today() in a cell that I use as a reference.

I believe this can be done with an IF statement combined with a Vlookup (forgive the syntax)

If({Sheet 1 dateRange1} = Today@row, Vlookup Employee@row, {VlookupRange1}, 2,0), IF({Sheet 2 dateRange2} = Today@row, Vlookup Employee@row, {VlookupRange2},2,0, "missing")

If information does not exist for the person Today on sheet 1, I need to lookup another value on sheet 2. If information does not exist anywhere, I want to return "missing".

Hope that makes sense! Any direction is appreciated.

Tags:

Best Answer

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓

    Hi @Matthew_Lanterman  

    I am assuming the date column is updating daily for each answer. You can try the below formula. The first condition is checking in the Sheet 1, if there is no answer then it will check Sheet 2.

    I hope this works for you

    =IF(JOIN(COLLECT({Answer}, {Employee Name}, Name@row, {Today Date}, TodayDate@row)) <> "", JOIN(COLLECT({Answer }, { Employee Name }, Name@row, { Today Date }, TodayDate@row)), IF(JOIN(COLLECT({Answer 2}, { Employee Name 2}, Name@row, { Today Date 2}, TodayDate@row)) <> "", JOIN(COLLECT({Answer 2}, { Employee Name 2}, }, Name@row, { Today Date 2}, TodayDate@row)), "Missing"))

     

    Regards

    Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Answer ✓

    Hi @Matthew_Lanterman  

    I am assuming the date column is updating daily for each answer. You can try the below formula. The first condition is checking in the Sheet 1, if there is no answer then it will check Sheet 2.

    I hope this works for you

    =IF(JOIN(COLLECT({Answer}, {Employee Name}, Name@row, {Today Date}, TodayDate@row)) <> "", JOIN(COLLECT({Answer }, { Employee Name }, Name@row, { Today Date }, TodayDate@row)), IF(JOIN(COLLECT({Answer 2}, { Employee Name 2}, Name@row, { Today Date 2}, TodayDate@row)) <> "", JOIN(COLLECT({Answer 2}, { Employee Name 2}, }, Name@row, { Today Date 2}, TodayDate@row)), "Missing"))

     

    Regards

    Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

  • Matthew_Lanterman
    Matthew_Lanterman ✭✭✭✭✭

    Hi @Amit Wadhwani - Thank you for the help. The formula is returning 'Missing' for all employees, and I know there is data for Today in both lookup sheets.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!