Help returning value between certain dates

[Dellboy]

Hello,

Hope some can help me.

I'm trying to create a formula that returns values between certain dates. It's been a while since I've used Excel and I'm strugglign at the moment.

See screenshot below if this was the raw data I would use;

Then, on this front sheet, I would like to return the values between the column E and F, in this instance, between 09/07/2023 and 11/07/23

Any help would be great appreciated.

 

[kevin9999]

Book1
	A	B	C	D	E	F	G
1			Date New	Expiry Date
2			09/07/2023	11/07/2023
3
4		Fruit	Company	Owner	Action	Date New	Expiry
5		Apple	Dell	Sam	Purchase	09/07/2023	11/07/2023
6		Apple	HP	Sam	Quote	16/07/2023	11/07/2023
7		Orange	HP	Bob	Purchase	10/07/2023	11/07/2023
8
Sheet2

Cell Formulas
Range	Formula
B5:G7	B5	=FILTER(Sheet1!A2:F7,(Sheet1!E2:E7>=C2)*(Sheet1!F2:F7<=D2),"")
Dynamic array formulas.

 

[Dellboy]

Thank you for this!

A couple of other things. Is it possible to filter the return values? i.e only return values for colums B,C,D and G? (not returning columns E and F).

Also, can I return the values based on a certain criteria, such as only returning values that have fruit as 'Apple' and 'Banana'.

 

[Dellboy]

Trying the IF function, but doesn't seem to work.

Also, if I add Index function and use {2,3,4,7} columns doesn't return columns B,C,D,G

 

[kevin9999]

Is it possible to filter the return values? i.e only return values for colums B,C,D and G? (not returning columns E and F).

Do you mean like this:

filter.xlsx
	A	B	C	D	E	F
1			Date New	Expiry Date
2			09/07/2023	11/07/2023
3
4		Fruit	Company	Owner	Expiry
5		Apple	Dell	Sam	11/07/2023
6		Apple	HP	Sam	11/07/2023
7		Orange	HP	Bob	11/07/2023
8
Sheet2

Cell Formulas
Range	Formula
B5:E7	B5	=FILTER(FILTER(Sheet1!A2:F7,(Sheet1!E2:E7>=C2)*(Sheet1!F2:F7<=D2),""),{1,1,1,0,0,1})
Dynamic array formulas.

You can probably tell within the braces {} which columns you want to return (1) and which you don't (0)

 

[Dellboy]

hmm, that doesn't seem to work for me

When I try index at the start of the formula, then add {2,3,4,7}), it only returns one line

 

[kevin9999]

When I try index at the start of the formula

Where is the "index" in the formula in post #5?

 

[Dellboy]

Apologies, tried using index again.

Got the filter working, realised I needed to add 1,0,0,1 etc... for all the columns or else I got a value error. Thanks for this!

One final thing, and sorry to be a pain, how can I add a filter that only looks up the value of something specific, e.g, I only want to return 'Apple' from the table?

 

[kevin9999]

No problem, you just add another criterion to the inner Filter function:

filter.xlsx
	B	C	D	E
1		Date New	Expiry Date
2		09/07/2023	11/07/2023
3
4	Fruit	Company	Owner	Expiry
5	Apple	Dell	Sam	11/07/2023
6	Apple	HP	Sam	11/07/2023
7
Sheet2

Cell Formulas
Range	Formula
B5:E6	B5	=FILTER(FILTER(Sheet1!A2:F7,(Sheet1!A2:A7="Apple")*(Sheet1!E2:E7>=C2)*(Sheet1!F2:F7<=D2),""),{1,1,1,0,0,1})
Dynamic array formulas.