Forum Discussion
IFS Function - producing a true if between two values
=IF(A2=$F$1,AND(INDEX($A$1:A2,LARGE(IF(ISNUMBER($A$1:A2),
ROW($A$1:A2)),1))=$G$1,INDEX(A2:$A$9,SMALL(IF(
ISNUMBER(A2:$A$9),ROW(A2:$A$9)-ROW(A1)),1))=$G$2),"")Thank you. In my first reply i didn't correctly understand what you want to do. With this formula the dates can be specified in cells G1 and G2. If someone doesn't work with Office 365 or Excel for the web or Excel 2021 the formula has to be entered with ctrl+shift+enter as an arrayformula. WAHR is TRUE and FALSCH is FALSE in german Excel.
=IF(A2=$F$1,AND(INDEX($A$1:A2,LARGE(IF(ISNUMBER($A$1:A2),ROW($A$1:A2)),1))<INDEX(A2:$A$9,SMALL(IF(ISNUMBER(A2:$A$9),ROW(A2:$A$9)-ROW(A1)),1))),"")
You can try this formula in cell B2 and fill down to B9. The name can be entered in cell F1 and the formula dynamically updates the results.
Thank you Oliver for taking the time to look at my query.
I was wondering if there is a way to do this if where it comes back as true only if between the specified dates. for example, in the solution you gave the Dave's come back as true despite one of them not being between 10/10/2023 and 11/10/2023.
For context, I have been given a list of people that enter the building, which is in much the same format as in the example: the date and below that the names of the people that entered on that date. this contain data for multiple dates all in one column. What I'm looking to do is get a yes or no to the question "did Dave enter the building between 10/10/2023 and 11/10/2023"
Hopefully this is a clearly explanation of my query.
apologies for being able to post the excel sheet I am working on itself, as the contents contain personal data.
Many Thanks
Benson
=IF(A2=$F$1,AND(INDEX($A$1:A2,LARGE(IF(ISNUMBER($A$1:A2),
ROW($A$1:A2)),1))=$G$1,INDEX(A2:$A$9,SMALL(IF(
ISNUMBER(A2:$A$9),ROW(A2:$A$9)-ROW(A1)),1))=$G$2),"")Thank you. In my first reply i didn't correctly understand what you want to do. With this formula the dates can be specified in cells G1 and G2. If someone doesn't work with Office 365 or Excel for the web or Excel 2021 the formula has to be entered with ctrl+shift+enter as an arrayformula. WAHR is TRUE and FALSCH is FALSE in german Excel.
- This is very useful, thank you Oliver! Hopefully I can implement this into my work.
Much Appreciated.
Benson
- Edit:
Hopefully this is a clearer explanation of my query.
Apologies for not being able to post the excel sheet I am working on itself, as the contents contain personal data.
