Nested IF statement with VLookup Formula

Matthew_Lanterman

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.

Amit Wadhwani

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/

Amit Wadhwani

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/

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.