How to Pull Data from a Date Range in Excel: 7 Ways

The Sales Data in the table below; B4:D13 cells contain the “Item”, “Date”, and “Sales” columns respectively. It demonstrate how to pull data from a date range in Excel using various features, tools, and functions of Excel.

Method 1 – Using the FILTER Function

Steps:

Go to cell B17 >> enter the formula below.

=FILTER(B5:D13,MONTH(C5:C13)=4,"No Info.")

Formula Breakdown

FILTER(B5:D13,MONTH(C5:C13)=4,”No Info.”) → filter a range or array. Here, B5:D13 is the array argument, while MONTH(C5:C13)=4 is the include argument that selects the values corresponding to the month of “April”. “No Info.” is the optional if_empty argument that is returned by the function if there are no matches.

Note: The FILTER function is available on Microsoft Excel 365, if you’re using an older version of Excel, then please check the second method.

Method 2 – Combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions

Steps:

Move to the B17 cell >> copy and paste the code below.

=IFERROR(INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROW(A1)), COLUMN(A1)), "No Info.")

Formula Breakdown

COLUMN(A1) → returns the column number of a cell reference.
- Output → 1
ROW(A1) → returns the serial number of a reference.
- Output → 1
MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) → returns the relative position of an item in an array matching the given value. Here, ROW($B$5:$B$13) is the lookup_value argument that refers to the “Item” column. Following, ROW($B$5:$B$13) represents the lookup_array argument from where the value is matched.
- Output → {1;2;3;4;5;6;7;8;9}
IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) → checks whether a condition is met and returns one value if TRUE and another value if. Here, ($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4) is the logical_test argument which prompts the IF function to return the value from MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) (value_if_true argument) otherwise it returns blank “” (value_if_false argument).
- Output → {1;2;””;””;5;6;7;””;9}
SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4),MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)) → becomes
- SMALL({1;2;””;””;5;6;7;””;9}, 1) → returns the kth smallest value in data set.
- Output → {1}
INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)), COLUMN(A1)) → becomes
- INDEX($B$5:$D$13, 1, 1) → returns a value at the intersection of a row and column in a given range. In this expression, the $B$5:$D$13 is the array argument which is the “Item” column. Next, 1 is the row_num argument that indicates the row location, while 1 is the column_num argument that indicates the column location.
- Output →”Playstation”
IFERROR(INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)), COLUMN(A1)), “No Info.”) → becomes
- IFERROR(“Playstation”, “No Info.”) → returns value_if_error if the expression has an error and the value of the expression itself otherwise. Here, “Playstation” is the value argument, and “No Info.” is the value_if_error argument.
- Output → “Playstation”

Observe the steps in real-time in the GIF below to copy the formula into the cell below.

Read More: VLOOKUP Date Range and Return Value in Excel

Method 3 – Using the Date Filter Feature

Steps:

Navigate to cell B16 >> click the Sort & Filter drop-down >> choose Filter.

Click the Down-arrow button >> jump to Date Filters >> select Custom Filter.

Select the “Date is after 1/1/2022 and is before 3/31/2022” criteria >> hit OK.

The results look like the image below.

Method 4 – Utilizing Excel Table

Steps:

Sarray B16:D25 array >> press the CTRL + T shortcut keys to insert Table.

Press the Down-arrow button >> choose the months, in this case, “January”, “February”, and “March” >> click OK.

The final table should look like the picture below.

Similar Readings

Method 5 – Implementing PivotTable

Steps:

Move to the B4 cell >> click on Insert >> press the PivotTable button >> select the New Worksheet option >> hit OK.

Drag the “Item”, “Date”, and “Sales” Fields into the “Rows”, “Values”, and “Filter” Areas respectively.

Press the Down-arrow button >> tick the Select Multiple Items box >> choose the dates according to your preference >> click OK.

The end results should look like the figure below.

Method 6 – Using PowerQuery

Steps:

Go to the B5 cell >> press CTRL + T keys to convert the dataset into a Table.

Move to the Data tab >> choose From Table/Range option.

Follow the steps in the GIF for a live demonstration to filter the dated range.

Press the Close & Load drop-down >> choose the Close & Load To option.

Check the Existing worksheet button >> enter the B16 cell reference >> press OK.

Subsequently, the final output appears in the image given below.

Method 7 – Applying VBA Code

Steps:

Navigate to the Developer tab >> click the Visual Basic button.

Go to the Insert tab >> select Module.

Copy the code below and paste it into the window as shown below.

Sub Extract_date_from_range()
    Dim Begin_date As Long, Finish_date As Long
    Begin_date = Range("G4").Value
    Finish_date = Range("G5").Value
    Range("C16:C25").AutoFilter field:=1, _
        Criteria1:=">=" & Begin_date, _
        Operator:=xlAnd, _
        Criteria2:="<=" & Finish_date
End Sub

Code Breakdown

The VBA code used to extract data from a date range.

The sub-routine is given a name, here it is Extract_date_from_range().
Define the variables Begin_date and Finish_date; assign the Long data type.
Use the Range.Value to set the cell references for the starting and ending dates, in this case, “1/1/2022” and “3/31/2022”.
Apply the Range.AutoFilter to filter the dates in between the starting and ending dates.