Forum Discussion

Copper Contributor

Oct 12, 2023

Solved

IFS Function - producing a true if between two values

Hi, I am trying to create a spread sheet where the raw data i am given is contained in one column. I am trying to find the solution using an IFS function. 10/10/2023 Dave Steph Neil 11/10...

excel

Formulas and Functions

OliverScheurich
Oct 12, 2023
benson88
=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.

OliverScheurich

Gold Contributor

Oct 12, 2023

benson88

=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.