Dynamic Range Driven by Criteria Selection - Excel and Power Query:

Source:

The input table includes columns for Date and Sales.

In separate range, dropdown is available to select the Start and End Dates.

DynamicRange.xlsx

Download XLSX • 12KB

Task:

Create a dynamic range that adjusts according to the selected Start and End Dates.

Solution:

Excel:

Method-1:

=LET(d,B3:B27,XLOOKUP(E5,d,d):XLOOKUP(F5,d,C3:C27))

Method-2:

=LET(d,B3:B35,INDEX(d,XMATCH(F5,d)):INDEX(C3:C35,XMATCH(E5,d)))

Method-3:

=LET(d,B3:B27,x,XMATCH(E5,d)-1,OFFSET(B3,x,,XMATCH(F5,d)-x,2))

Method-4:

=LET(x,XMATCH(E5:F5,B3:B35),TAKE(DROP(B3:C35,MIN(x)-1),MAX(x)-MIN(x)+1))

Method-5:

Suggested by Rick Rothstein.

=FILTER(B3:C18,ABS(B3:B18-(E5+F5)/2)<=(F5-E5)/2)

Method-6:

Suggested by Peter Bartholomew.

=LET(crit,MAP(B3:B27,LAMBDA(d,MEDIAN(d,F5,E5)=d)),FILTER(B3:C27,crit))

Method-7:

Suggested by Peter de B.

=FILTER(B3:C27,MOD(SCAN(,BYROW(B3:B27=E5:F5,OR),SUM),2))

Power Query:

Method-8:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 fx = (Tbl) => Table.FirstValue(Tbl),

 Result = Table.RemoveLastN(Table.Skip(Source("Table1"), each [Date] < fx(Source("Table2"))), each [Date] > fx(Source("Table2")[[End Date]]))

in

 Result

Method-9:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 fx = (Tbl) => Table.FirstValue(Tbl),

 Result = Table.SelectRows(Source("Table1"), (f) => f[Date] >= fx(Source("Table2")) and f[Date] <= fx(Source("Table2")[[End Date]]))

in

 Result