Index/Match?

[Cam147]

Possibly a 2 condition index match formula? Data sheet is in a separate spreadsheet to the formula sheet.

I am looking to return the relevant data (into cell G4, H4 & I4 on formula sheet) from the Data sheet (Task - column C, Frequency – Column B & Who – Column E) if the due date (column G on Data sheet) falls on or between the 2 dates (Q2&Q8 on Formula sheet) & Cell G2 in Formula sheet.


If there are multiple results I need it to return all of them.

Any help would be greatly appreciated.
Thanks Cam

 

[Eric W]

Starting with this:

Book1
	A	B	C	D	E	F	G
1		Frequency	Task	Department	Who		Due Date
2		Weekly	1	A1	A		3/4/2022
3		Weekly	2	B2	B		3/4/2022
4		Weekly	3	A1	C		3/5/2022
5		Weekly	4	B2	D		9/9/2022
6		Weekly	5	A1	E		10/1/2022
7		Monthly	6	B2	F		3/10/2022
8		Monthly	7	A1	G		7/7/2022
9		Quarterly	8	B2	H		3/8/2022
10		Monthly	9	A1	I		3/6/2022
11		Quarterly	10	B2	J		8/8/2022
12		Weekly	11	A1	K		9/9/2022
13		Quarterly	12	B2	L		10/10/2022
14		Monthly	13	A1	M		11/11/2022
Data Sheet

You can use formulas like this on your formula sheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							Upcoming Tasks
2							A1										3/1/2022
3							Task	Frequency	Who
4							1	Weekly	A
5							3	Weekly	C
6							9	Monthly	I
7
8							B2										3/10/2022
9							Task	Frequency	Who
10							2	Weekly	B
11							6	Monthly	F
12							8	Quarterly	H
13
Formula Sheet

Cell Formulas
Range	Formula
G4:G7	G4	=IFERROR(INDEX('Data Sheet'!$C:$C,AGGREGATE(15,6,ROW('Data Sheet'!$C$2:$C$50)/('Data Sheet'!$D$2:$D$50=$G$2)/('Data Sheet'!$G$2:$G$50>=$Q$2)/('Data Sheet'!$G$2:$G$50<=$Q$8),ROWS(G$4:G4))),"")
H4:H7	H4	=IFERROR(INDEX('Data Sheet'!$B:$B,AGGREGATE(15,6,ROW('Data Sheet'!$C$2:$C$50)/('Data Sheet'!$D$2:$D$50=$G$2)/('Data Sheet'!$G$2:$G$50>=$Q$2)/('Data Sheet'!$G$2:$G$50<=$Q$8),ROWS(G$4:G4))),"")
I4:I7	I4	=IFERROR(INDEX('Data Sheet'!$E:$E,AGGREGATE(15,6,ROW('Data Sheet'!$C$2:$C$50)/('Data Sheet'!$D$2:$D$50=$G$2)/('Data Sheet'!$G$2:$G$50>=$Q$2)/('Data Sheet'!$G$2:$G$50<=$Q$8),ROWS(G$4:G4))),"")
G10:G13	G10	=IFERROR(INDEX('Data Sheet'!$C:$C,AGGREGATE(15,6,ROW('Data Sheet'!$C$2:$C$50)/('Data Sheet'!$D$2:$D$50=$G$8)/('Data Sheet'!$G$2:$G$50>=$Q$2)/('Data Sheet'!$G$2:$G$50<=$Q$8),ROWS(G$10:G10))),"")
H10:H13	H10	=IFERROR(INDEX('Data Sheet'!$B:$B,AGGREGATE(15,6,ROW('Data Sheet'!$C$2:$C$50)/('Data Sheet'!$D$2:$D$50=$G$8)/('Data Sheet'!$G$2:$G$50>=$Q$2)/('Data Sheet'!$G$2:$G$50<=$Q$8),ROWS(G$10:G10))),"")
I10:I13	I10	=IFERROR(INDEX('Data Sheet'!$E:$E,AGGREGATE(15,6,ROW('Data Sheet'!$C$2:$C$50)/('Data Sheet'!$D$2:$D$50=$G$8)/('Data Sheet'!$G$2:$G$50>=$Q$2)/('Data Sheet'!$G$2:$G$50<=$Q$8),ROWS(G$10:G10))),"")

Incidentally, please look into the XL2BB tool (see the link in my signature). It's easy to download, install, and use. And it makes it MUCH easier for the helpers here to work with your sample data, we don't need to retype everything, which is what we have to do if you just post a picture.

Hope this helps!