Combining INDEX MATCH with multiple criteria, when one of the criterion is within a date range

[mjmax]

Hello, I've been using this formula to INDEX the Correct rate based on the Date Range the date falls.
=INDEX($B$7:$B$11,MIN(IF((J5>=$E$7:$E$11)*(J5<=$F$7:$F$11),MATCH(ROW($B$7:$B$11),ROW($B$7:$B$11)))))

This works well, but I have to manually write the formula for each Hotel and Room Type. But things get complicated when there are multiple options for hotels and room. I end up with really large IF statements combined with this basic index formula. I thought I could combine this INDEX formula with something like =INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))} to first Match the "Rate Code" (D2:D21) (Textjoin of Hotel and Room) ((M5) then match the row to the date range. J5>=(E2:E21)*J5<=(F2:F21) to return the Rate (B2:B21)

Thanks in advance! Love this forum!

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Hotel	Rate	Room	RateCode	Start Date	End Date
2	ABC	100	1	ABC1	12/1/2022	12/23/2021
3	ABC	200	1	ABC1	12/24/2021	1/5/2022
4	ABC	100	1	ABC1	1/6/2022	3/31/2022				Date	Hotel	Room	RateCode	Manual Rate
5	ABC	200	1	ABC1	4/1/2022	4/26/2022				4/3/2021	ABC	2	ABC2	300
6	ABC	100	1	ABC1	4/27/2022	6/23/2022				4/4/2021	ABC	2	ABC2	300
7	ABC	300	2	ABC2	12/1/2022	12/23/2021				4/5/2021	ABC	2	ABC2	300
8	ABC	400	2	ABC2	12/24/2021	1/5/2022				4/6/2021	ABC	2	ABC2	300
9	ABC	300	2	ABC2	1/6/2022	3/31/2022				4/7/2021	BBC	1	BBC1	600
10	ABC	400	2	ABC2	4/1/2022	4/26/2022				4/8/2021	BBC	1	BBC1	600
11	ABC	300	2	ABC2	4/27/2022	6/23/2022				4/9/2021	BBC	1	BBC1	600
12	BBC	600	1	BBC1	12/1/2022	12/23/2021				4/10/2021	BBC	1	BBC1	600
13	BBC	700	1	BBC1	12/24/2021	1/5/2022				4/11/2021	BBC	1	BBC1	600
14	BBC	600	1	BBC1	1/6/2022	3/31/2022
15	BBC	700	1	BBC1	4/1/2022	4/26/2022
16	BBC	600	1	BBC1	4/27/2022	6/23/2022
17	BBC	800	2	BBC2	12/1/2022	12/23/2021
18	BBC	900	2	BBC2	12/24/2021	1/5/2022
19	BBC	600	2	BBC2	1/6/2022	3/31/2022
20	BBC	700	2	BBC2	4/1/2022	4/26/2022
21	BBC	300	2	BBC2	4/27/2022	6/23/2022
22
Sheet1

Cell Formulas
Range	Formula
M5:M13	M5	=TEXTJOIN(,TRUE,K5,L5)
N5:N8	N5	=INDEX($B$7:$B$11,MIN(IF((J5>=$E$7:$E$11)*(J5<=$F$7:$F$11),MATCH(ROW($B$7:$B$11),ROW($B$7:$B$11)))))
N9:N13	N9	=INDEX($B$12:$B$16,MIN(IF((J9>=$E$12:$E$16)*(J9<=$F$12:$F$16),MATCH(ROW($B$12:$B$16),ROW($B$12:$B$16)))))
D2:D21	D2	=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mjmax]

Where are you seeing an overlap? The dates represent 12/1/2021 - 6/23/2022 for each hotel and room 1/1/1900 - 11/30/2022 represents the dates before and 6/24/2022 - 1/1/3333 represents all dates after the date range. If you are seeing repeats, you may be referring to a different hotel and/or Room.

12/1/2021​	12/23/2021​
12/24/2021​	1/5/2022​
1/6/2022​	3/31/2022​
4/1/2022​	4/26/2022​
4/27/2022​	6/23/2022​
1/1/1900​	11/30/2022​
6/24/2022​	1/1/3333​

 

[Fluff]

On row 11 you have 6th Jan 22 to 31st Mar 22 & then on row 14 you have 1st Jan 1900 to 30th Nov 22.
Both of those ranges include 28th Feb 22, also both rows 21 & 22 include the date of 8th Jul 22

 

[mjmax]

Ok. I'm sorry. That is a typo. ? Thank you so much! ? This is much easier then I thought it would be. I'm sorry for my lack of attention to detail.

To anyone referring to this horrible example of my inability to full comprehend, but is looking for Fluff's solution to this problem here it is!

=FILTER($B$2:$B$200,($A$2:$A$200=K5)*($C$2:$C$200=L5)*($E$2:$E$200<=J5)*($F$2:$F$200>=J5))

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M		O	P	Q
1	Hotel	Rate	Room	RateCode	Start Date	End Date
2	ABC	100	1	ABC1	12/1/2021	12/23/2021
3	ABC	200	1	ABC1	12/24/2021	1/5/2022
4	ABC	100	1	ABC1	1/6/2022	3/31/2022				Date	Hotel	Room	RateCode		Looking for a easier formula to do this	The correct Formula!
5	ABC	200	1	ABC1	4/1/2022	4/26/2022				2/28/2022	ABC	2	ABC2		300	300
6	ABC	100	1	ABC1	4/27/2022	6/23/2022				6/2/2022	BBC	1	BBC1		600	600
7	ABC	N/A	1	ABC1	1/1/1900	11/30/2021				4/6/2022	ABC	1	ABC1		200	200
8	ABC	N/A	1	ABC1	6/24/2022	1/1/3333				12/16/2021	ABC	2	ABC2		300	300
9	ABC	300	2	ABC2	12/1/2021	12/23/2021				6/5/2022	BBC	1	BBC1		600	600
10	ABC	400	2	ABC2	12/24/2021	1/5/2022				12/26/2021	ABC	1	ABC1		200	200
11	ABC	300	2	ABC2	1/6/2022	3/31/2022				2/6/2022	ABC	2	ABC2		300	300
12	ABC	400	2	ABC2	4/1/2022	4/26/2022				6/18/2022	BBC	2	BBC2		300	300
13	ABC	300	2	ABC2	4/27/2022	6/23/2022				1/1/2021	BBC	1	BBC1		N/A	N/A
14	ABC	N/A	2	ABC2	1/1/1900	11/30/2021
15	ABC	N/A	2	ABC2	6/24/2022	1/1/3333
16	BBC	600	1	BBC1	12/1/2021	12/23/2021
17	BBC	700	1	BBC1	12/24/2021	1/5/2022
18	BBC	600	1	BBC1	1/6/2022	3/31/2022
19	BBC	700	1	BBC1	4/1/2022	4/26/2022
20	BBC	600	1	BBC1	4/27/2022	6/23/2022
21	BBC	N/A	1	BBC1	1/1/1900	11/30/2021
22	BBC	N/A	1	BBC1	6/24/2022	1/1/3333
23	BBC	800	2	BBC2	12/1/2021	12/23/2021
24	BBC	900	2	BBC2	12/24/2021	1/5/2022
25	BBC	600	2	BBC2	1/6/2022	3/31/2022
26	BBC	700	2	BBC2	4/1/2022	4/26/2022
27	BBC	300	2	BBC2	4/27/2022	6/23/2022
28	BBC	N/A	2	BBC2	1/1/1900	11/30/2021
29	BBC	N/A	2	BBC2	6/24/2022	1/1/3333
30
Sheet1

Cell Formulas
Range	Formula
M5	M5	=TEXTJOIN(,TRUE,K5,L5)
O5	O5	=IF(M5=$D$2,INDEX($B$2:$B$8,MIN(IF((J5>=$E$2:$E$8)*(J5<=$F$2:$F$8),MATCH(ROW($B$2:$B$8),ROW($B$2:$B$8))))),IF(M5=$D$9,INDEX($B$9:$B$15,MIN(IF((J5>=$E$9:$E$15)*(J5<=$F$9:$F$15),MATCH(ROW($B$9:$B$15),ROW($B$9:$B$15))))),IF(M5=$D$16,INDEX($B$16:$B$22,MIN(IF((J5>=$E$16:$E$22)*(J5<=$F$16:$F$22),MATCH(ROW($B$16:$B$22),ROW($B$16:$B$22))))),IF(M5=$D$23,INDEX($B$23:$B$29,MIN(IF((J5>=$E$23:$E$29)*(J5<=$F$23:$F$29),MATCH(ROW($B$23:$B$29),ROW($B$23:$B$29))))),"No Match"))))
P5:P8,P10:P13	P5	=FILTER($B$2:$B$200,($A$2:$A$200=K5)*($C$2:$C$200=L5)*($E$2:$E$200<=J5)*($F$2:$F$200>=J5))
M6:M13	M6	=TEXTJOIN(,TRUE,K6,L6)
O6:O13	O6	=IF(M6=$D$2,INDEX($B$2:$B$6,MIN(IF((J6>=$E$2:$E$6)*(J6<=$F$2:$F$6),MATCH(ROW($B$2:$B$6),ROW($B$2:$B$6))))),IF(M6=$D$9,INDEX($B$9:$B$13,MIN(IF((J6>=$E$9:$E$13)*(J6<=$F$9:$F$13),MATCH(ROW($B$9:$B$13),ROW($B$9:$B$13))))),IF(M6=$D$16,INDEX($B$16:$B$22,MIN(IF((J6>=$E$16:$E$22)*(J6<=$F$16:$F$22),MATCH(ROW($B$16:$B$22),ROW($B$16:$B$22))))),IF(M6=$D$23,INDEX($B$23:$B$29,MIN(IF((J6>=$E$23:$E$29)*(J6<=$F$23:$F$29),MATCH(ROW($B$23:$B$29),ROW($B$23:$B$29))))),"No Match"))))
P9	P9	=FILTER($B$2:$B$200,($A$2:$A$200=K9)*($C$2:$C$200=L9)*($E$2:$E$200<=J9)*($F$2:$F$200>=J9))
D2:D29	D2	=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Why have to array confirmed the formula ( ie with Ctrl Shift Enter) it should just be normally entered.

 

[mjmax]

Good point. I think just habit. Her is without the array.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K		M	N
1	Hotel	Rate	Room	RateCode	Start Date	End Date
2	ABC	100	1	ABC1	12/1/2021	12/23/2021
3	ABC	200	1	ABC1	12/24/2021	1/5/2022
4	ABC	100	1	ABC1	1/6/2022	3/31/2022		Date	Hotel	Room	RateCode		Looking for a easier formula to do this	The correct Formula!
5	ABC	200	1	ABC1	4/1/2022	4/26/2022		2/28/2022	ABC	2	ABC2		300	300
6	ABC	100	1	ABC1	4/27/2022	6/23/2022		6/2/2022	BBC	1	BBC1		600	600
7	ABC	N/A	1	ABC1	1/1/1900	11/30/2021		4/6/2022	ABC	1	ABC1		200	200
8	ABC	N/A	1	ABC1	6/24/2022	1/1/3333		12/16/2021	ABC	2	ABC2		300	300
9	ABC	300	2	ABC2	12/1/2021	12/23/2021		6/5/2022	BBC	1	BBC1		600	600
10	ABC	400	2	ABC2	12/24/2021	1/5/2022		12/26/2021	ABC	1	ABC1		200	200
11	ABC	300	2	ABC2	1/6/2022	3/31/2022		2/6/2022	ABC	2	ABC2		300	300
12	ABC	400	2	ABC2	4/1/2022	4/26/2022		6/18/2022	BBC	2	BBC2		300	300
13	ABC	300	2	ABC2	4/27/2022	6/23/2022		1/1/2021	BBC	1	BBC1		N/A	N/A
14	ABC	N/A	2	ABC2	1/1/1900	11/30/2021
15	ABC	N/A	2	ABC2	6/24/2022	1/1/3333
16	BBC	600	1	BBC1	12/1/2021	12/23/2021
17	BBC	700	1	BBC1	12/24/2021	1/5/2022
18	BBC	600	1	BBC1	1/6/2022	3/31/2022
19	BBC	700	1	BBC1	4/1/2022	4/26/2022
20	BBC	600	1	BBC1	4/27/2022	6/23/2022
21	BBC	N/A	1	BBC1	1/1/1900	11/30/2021
22	BBC	N/A	1	BBC1	6/24/2022	1/1/3333
23	BBC	800	2	BBC2	12/1/2021	12/23/2021
24	BBC	900	2	BBC2	12/24/2021	1/5/2022
25	BBC	600	2	BBC2	1/6/2022	3/31/2022
26	BBC	700	2	BBC2	4/1/2022	4/26/2022
27	BBC	300	2	BBC2	4/27/2022	6/23/2022
28	BBC	N/A	2	BBC2	1/1/1900	11/30/2021
29	BBC	N/A	2	BBC2	6/24/2022	1/1/3333
30
Sheet1

Cell Formulas
Range	Formula
K5	K5	=TEXTJOIN(,TRUE,I5,J5)
M5	M5	=IF(K5=$D$2,INDEX($B$2:$B$8,MIN(IF((H5>=$E$2:$E$8)*(H5<=$F$2:$F$8),MATCH(ROW($B$2:$B$8),ROW($B$2:$B$8))))),IF(K5=$D$9,INDEX($B$9:$B$15,MIN(IF((H5>=$E$9:$E$15)*(H5<=$F$9:$F$15),MATCH(ROW($B$9:$B$15),ROW($B$9:$B$15))))),IF(K5=$D$16,INDEX($B$16:$B$22,MIN(IF((H5>=$E$16:$E$22)*(H5<=$F$16:$F$22),MATCH(ROW($B$16:$B$22),ROW($B$16:$B$22))))),IF(K5=$D$23,INDEX($B$23:$B$29,MIN(IF((H5>=$E$23:$E$29)*(H5<=$F$23:$F$29),MATCH(ROW($B$23:$B$29),ROW($B$23:$B$29))))),"No Match"))))
N5:N13	N5	=FILTER($B$2:$B$200,($A$2:$A$200=I5)*($C$2:$C$200=J5)*($E$2:$E$200<=H5)*($F$2:$F$200>=H5))
K6:K13	K6	=TEXTJOIN(,TRUE,I6,J6)
M6:M13	M6	=IF(K6=$D$2,INDEX($B$2:$B$6,MIN(IF((H6>=$E$2:$E$6)*(H6<=$F$2:$F$6),MATCH(ROW($B$2:$B$6),ROW($B$2:$B$6))))),IF(K6=$D$9,INDEX($B$9:$B$13,MIN(IF((H6>=$E$9:$E$13)*(H6<=$F$9:$F$13),MATCH(ROW($B$9:$B$13),ROW($B$9:$B$13))))),IF(K6=$D$16,INDEX($B$16:$B$22,MIN(IF((H6>=$E$16:$E$22)*(H6<=$F$16:$F$22),MATCH(ROW($B$16:$B$22),ROW($B$16:$B$22))))),IF(K6=$D$23,INDEX($B$23:$B$29,MIN(IF((H6>=$E$23:$E$29)*(H6<=$F$23:$F$29),MATCH(ROW($B$23:$B$29),ROW($B$23:$B$29))))),"No Match"))))
D2:D29	D2	=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

In future please mark the post that solved the the issue as the colution & not your post saying that it works. Thanks