Excel - listing data from all rows that fall between two dates, while ignoring blank entries

Question

I can't get my head around the formula required to return a list of data from all rows in excel that fall between two specific dates.

Sheet1 contains the following data:

Date    Region  Reference
01/01/2015  B   4458
01/02/2015  B   6635
01/02/2016  A   3175
01/03/2016  C   2458
01/03/2016  A   2194
01/04/2016  A   3594
01/04/2016  C   
01/05/2016  C   1654
12/05/2016  B   3648
01/06/2016  B   
01/06/2016  B   3296

In Sheet2, I specify a start date in cell C2 and an end date in cell C3.

I would like to display a list in sheet2 that gives me a list of all reference numbers in sheet1 that fall within that date range. I will also however want it to ignore any cells that are blank and do not contain a reference number.

I've tried a few things to get this working, and I think an array formula using an INDEX function is probably the best bet, but I'm struggling to get the date range aspect to work.

Is anybody able to help please?

Thanks

Answer 1

I would advise that you format your data as a table if you haven't already as it makes the index function much easier to read.

The formula below should do what you require. The * in the if statement is like an "and". -4 near the end of the formula is the number of rows betwen the source table and the top of the sheet it's on. The last 1 at the end of the formula is the column that you want to take data from much like you have in a vlookup. remember to use SHIFT+CTRL+ENTER to make it an array formula.

=IFERROR(INDEX(Table1,SMALL(IF((Table1[Date]>=$C$2)*(Table1[Date]<=$C$3)*(Table1[Reference]<>""),ROW(Table1)-4),ROW(1:1)),1),"")

Formula if you don't want to format as table

=IFERROR(INDEX(Sheet2!$C$5:$E$15,SMALL(IF((Sheet2!$C$5:$C$15>=$C$2)*(Sheet2!$C$5:$C$15<=$C$3)*(Sheet2!$E$5:$E$15<>""),ROW(Sheet2!$C$5:$E$15)-4),ROW(1:1)),1),"")

Using the formula

I't not advisible to copy and paste this formula from one cell to another but instead copy the formula text and paste it to avoid parts of the formula changing.

keep an eye on the ROW(1:1) at the end of the formula, it specifies which row from the matching results will be extracted from the data and it should increment when you autofill the formula down the sheet.