Hi all -
I've actually written out a formula to do what I need but it's a beast, so I'm hoping someone can show me a simpler way.
In one worksheet I have a column of titles. In the next columns I have start date (1), end date (1), start date (2), end date (2), etc.
In a second worksheet I have a column of dates and a column of the titles. In the third column I need to return "In" or "Out" depending on the dates in the first worksheet.
If the date in the second worksheet falls inside the range of start&end 1, start&end 2, etc. then it should say "OUT". If it falls outside any of the ranges it should say "IN".
Because the names of the date columns are the same except for the number, I thought using fuzzy logic within the VLOOKUP/MATCH would work, but it still only returns the first instance (start date (1))
The formula I've written basically goes through every set of dates inside a very complicated IF statement. Is there a way to write out the formula so it looks across the whole array of dates before returning the value?
Here - if you're interested - is the formula i wrote:
=IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!C:G,5,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$I,7,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$K,9,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$M,11,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$O,13,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$Q,15,FALSE))),1,0)=1,1,0))))))
I've actually written out a formula to do what I need but it's a beast, so I'm hoping someone can show me a simpler way.
In one worksheet I have a column of titles. In the next columns I have start date (1), end date (1), start date (2), end date (2), etc.
In a second worksheet I have a column of dates and a column of the titles. In the third column I need to return "In" or "Out" depending on the dates in the first worksheet.
If the date in the second worksheet falls inside the range of start&end 1, start&end 2, etc. then it should say "OUT". If it falls outside any of the ranges it should say "IN".
Because the names of the date columns are the same except for the number, I thought using fuzzy logic within the VLOOKUP/MATCH would work, but it still only returns the first instance (start date (1))
The formula I've written basically goes through every set of dates inside a very complicated IF statement. Is there a way to write out the formula so it looks across the whole array of dates before returning the value?
Here - if you're interested - is the formula i wrote:
=IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!C:G,5,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$I,7,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$K,9,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$M,11,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$O,13,FALSE))),1,0)=1,1,IF(IF(AND('Raw Display'!$A3>=VLOOKUP('Raw Display'!$C3,Booked!$C:$ABC,(MATCH("Start Date*",Booked!$1:$1,0)),FALSE),('Raw Display'!$A3<=VLOOKUP('Raw Display'!$C3,Booked!$C:$Q,15,FALSE))),1,0)=1,1,0))))))
