yogi_Mark Names Of Actors That Are Out Based On Form Submittals Of Date Out And Date Back

Google Spreadsheet   Post  #2178

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-07-2017

question by SMintheHat:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LkhrFcDZ4_Y;context-place=forum/docs
Vlookup not searching through duplicates

Hello all!

I've gotten a lot of advice from this forum and have created the following sheet to help with work:

https://docs.google.com/spreadsheets/d/1UsQo7fnoxvO49kN1MqCcM_-UUV8NNVO6_hGX82OppoE/edit?usp=sharing

In short it is a way to tell which actor is out based on data entered in the form linked to the sheet and the date at the top of the sheet.

The main search formula is:

=iferror(if(and(vlookup(A4,'Actors Out'!$A$2:$A,1,0),$B$1>=index('Actors Out'!$B$2:$B,match(A4,'Actors Out'!$A$2:$A,0)),$B$1<index('Actors Out'!$C$2:$C,match(A4,'Actors Out'!$A$2:$A,0))),"OUT",""),"")

The problem I'm having is that vlookup is stopping at the first actors name which shows up in the Actors Out form. When there is someone with multiple days out(Example AMY) "OUT" will show up as out when B1 is between 5/30/17 and 6/1/17 but not when B1 is between 6/8/17 and 6/10/17.

Please let me know if there is another to find if the actor will be out based on the cell B1 and the information being entered into the google form.

Thank you!