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

M

mjmax

New Member
Joined
Aug 10, 2018
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC112/1/202212/23/2021
3ABC2001ABC112/24/20211/5/2022
4ABC1001ABC11/6/20223/31/2022DateHotelRoomRateCodeManual Rate
5ABC2001ABC14/1/20224/26/20224/3/2021ABC2ABC2300
6ABC1001ABC14/27/20226/23/20224/4/2021ABC2ABC2300
7ABC3002ABC212/1/202212/23/20214/5/2021ABC2ABC2300
8ABC4002ABC212/24/20211/5/20224/6/2021ABC2ABC2300
9ABC3002ABC21/6/20223/31/20224/7/2021BBC1BBC1600
10ABC4002ABC24/1/20224/26/20224/8/2021BBC1BBC1600
11ABC3002ABC24/27/20226/23/20224/9/2021BBC1BBC1600
12BBC6001BBC112/1/202212/23/20214/10/2021BBC1BBC1600
13BBC7001BBC112/24/20211/5/20224/11/2021BBC1BBC1600
14BBC6001BBC11/6/20223/31/2022
15BBC7001BBC14/1/20224/26/2022
16BBC6001BBC14/27/20226/23/2022
17BBC8002BBC212/1/202212/23/2021
18BBC9002BBC212/24/20211/5/2022
19BBC6002BBC21/6/20223/31/2022
20BBC7002BBC24/1/20224/26/2022
21BBC3002BBC24/27/20226/23/2022
22
Sheet1
Cell Formulas
RangeFormula
M5:M13M5=TEXTJOIN(,TRUE,K5,L5)
N5:N8N5=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:N13N9=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:D21D2=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
To be clear, I am looking for someone to help me write a formula that Matches the Rate Code to then find the row that matches the date.

Example.xlsx
ABCDEFGHIJKLMNO
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC112/1/202212/23/2021
3ABC2001ABC112/24/20211/5/2022
4ABC1001ABC11/6/20223/31/2022DateHotelRoomRateCodeManual RateFull Index of Table to find correct rate
5ABC2001ABC14/1/20224/26/20224/3/2021ABC2ABC2300Formula that Matches M to D2:D21, then finds J inbetween E2:E21 amd F2:F21 but only looking at the rows M was Matched to
6ABC1001ABC14/27/20226/23/20224/4/2021ABC2ABC2300
7ABC3002ABC212/1/202212/23/20214/5/2021ABC2ABC2300
8ABC4002ABC212/24/20211/5/20224/6/2021ABC2ABC2300
9ABC3002ABC21/6/20223/31/20224/7/2021BBC1BBC1600
10ABC4002ABC24/1/20224/26/20224/8/2021BBC1BBC1600
11ABC3002ABC24/27/20226/23/20224/9/2021BBC1BBC1600
12BBC6001BBC112/1/202212/23/20214/10/2021BBC1BBC1600
13BBC7001BBC112/24/20211/5/20224/11/2021BBC1BBC1600
14BBC6001BBC11/6/20223/31/2022
15BBC7001BBC14/1/20224/26/2022
16BBC6001BBC14/27/20226/23/2022
17BBC8002BBC212/1/202212/23/2021
18BBC9002BBC212/24/20211/5/2022
19BBC6002BBC21/6/20223/31/2022
20BBC7002BBC24/1/20224/26/2022
21BBC3002BBC24/27/20226/23/2022
22
Sheet1
Cell Formulas
RangeFormula
M5:M13M5=TEXTJOIN(,TRUE,K5,L5)
N5:N8N5=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:N13N9=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:D21D2=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Hello, Thank you for the information. I have updated my profile. I use 365 on Windows.
 
Upvote 0
Thanks for that, as none of your dates in col J matched the dates in cols E & F, I have changed them.
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC101/12/202123/12/2021
3ABC2001ABC124/12/202105/01/2022
4ABC1001ABC106/01/202231/03/2022DateHotelRoomRateCodeManual Rate
5ABC2001ABC101/04/202226/04/202203/04/2022ABC2ABC2400
6ABC1001ABC127/04/202223/06/202204/04/2022ABC2ABC2400
7ABC3002ABC201/12/202123/12/202105/04/2022ABC2ABC2400
8ABC4002ABC224/12/202105/01/202206/04/2022ABC2ABC2400
9ABC3002ABC206/01/202231/03/202207/04/2022BBC1BBC1700
10ABC4002ABC201/04/202226/04/202208/04/2022BBC1BBC1700
11ABC3002ABC227/04/202223/06/202209/04/2022BBC1BBC1700
12BBC6001BBC101/12/202223/12/202110/04/2022BBC1BBC1700
13BBC7001BBC124/12/202105/01/202211/04/2022BBC1BBC1700
14BBC6001BBC106/01/202231/03/2022
15BBC7001BBC101/04/202226/04/2022
16BBC6001BBC127/04/202223/06/2022
17BBC8002BBC201/12/202223/12/2021
18BBC9002BBC224/12/202105/01/2022
19BBC6002BBC206/01/202231/03/2022
20BBC7002BBC201/04/202226/04/2022
21BBC3002BBC227/04/202223/06/2022
22
Main
Cell Formulas
RangeFormula
M5:M13M5=TEXTJOIN(,TRUE,K5,L5)
N5:N13N5=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))
D2:D21D2=TEXTJOIN(,TRUE,A2,C2,)
 
Upvote 0
Solution
Thanks but that defeats the entire point of the formula. The dates in column J will rarely match the dates in E & F. The formula below looks at the date in J5 and finds the Row where the date J5 would exist between the date in E and F. I had forgotten to add the dates that don't exist to the data table. This allows the formula to find the correct date in between E&F.


I'm afraid I didn't provide a good enough example. In the example below I show how I use this formula combine with IF function to pull the correct rate based on the Date and Rate Code. I'm thinking I can somehow combine the formula above with the INDEX match formula that looks for multiple criteria in order to perform this action without setting the IF statement to look for the Rate code.

This is the long crazy formula I am using currently:
Excel Formula: 
=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"))))

Example Photo.JPG


Here is the updated example. i hope this makes it more clear.

Example.xlsx
ABCDEFGHIJKLMOP
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC112/1/202112/23/2021
3ABC2001ABC112/24/20211/5/2022
4ABC1001ABC11/6/20223/31/2022DateHotelRoomRateCodeLooking for a easier formula to do this
5ABC2001ABC14/1/20224/26/20222/28/2022ABC2ABC2300
6ABC1001ABC14/27/20226/23/20227/8/2022BBC1BBC1N/A
7ABCN/A1ABC11/1/190011/30/202210/15/2022ABC1ABC1100
8ABCN/A1ABC16/24/202251/1/33337/5/2022ABC2ABC2300
9ABC3002ABC212/1/202112/23/20216/5/2022BBC1BBC1600
10ABC4002ABC212/24/20211/5/202212/26/2021ABC1ABC1200
11ABC3002ABC21/6/20223/31/20225/25/2021ABC2ABC2300
12ABC4002ABC24/1/20224/26/20226/18/2021BBC2BBC2N/A
13ABC3002ABC24/27/20226/23/20224/11/2021BBC1BBC1N/A
14ABCN/A2ABC21/1/190011/30/2022
15ABCN/A2ABC26/24/202251/1/3333
16BBC6001BBC112/1/202112/23/2021
17BBC7001BBC112/24/20211/5/2022
18BBC6001BBC11/6/20223/31/2022
19BBC7001BBC14/1/20224/26/2022
20BBC6001BBC14/27/20226/23/2022
21BBCN/A1BBC11/1/190011/30/2022
22BBCN/A1BBC16/24/202251/1/3333
23BBC8002BBC212/1/202112/23/2021
24BBC9002BBC212/24/20211/5/2022
25BBC6002BBC21/6/20223/31/2022
26BBC7002BBC24/1/20224/26/2022
27BBC3002BBC24/27/20226/23/2022
28BBCN/A2BBC21/1/190011/30/2022
29BBCN/A2BBC26/24/202251/1/3333
30
31
Sheet1
Cell Formulas
RangeFormula
M5M5=TEXTJOIN(,TRUE,K5,L5)
O5O5=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"))))
M6:M13M6=TEXTJOIN(,TRUE,K6,L6)
O6:O13O6=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"))))
D2:D29D2=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Did you actually try what I suggested, or did you just reject it out-of hand?
 
Upvote 0
I did. It spills.

Example.xlsx
ABCDEFGHIJKLMOPQRSTUVWXY
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC112/1/202112/23/2021
3ABC2001ABC112/24/20211/5/2022
4ABC1001ABC11/6/20223/31/2022DateHotelRoomRateCodeLooking for a easier formula to do this=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))
5ABC2001ABC14/1/20224/26/20222/28/2022ABC2ABC2300#SPILL!
6ABC1001ABC14/27/20226/23/20227/8/2022BBC1BBC1N/A#SPILL!
7ABCN/A1ABC11/1/190011/30/202210/15/2022ABC1ABC1100#SPILL!
8ABCN/A1ABC16/24/20221/1/33337/5/2022ABC2ABC2300#SPILL!
9ABC3002ABC212/1/202112/23/20216/5/2022BBC1BBC1600#SPILL!
10ABC4002ABC212/24/20211/5/202212/26/2021ABC1ABC1200#SPILL!
11ABC3002ABC21/6/20223/31/20225/25/2021ABC2ABC2300N/A
12ABC4002ABC24/1/20224/26/20226/18/2021BBC2BBC2N/AN/A
13ABC3002ABC24/27/20226/23/20224/11/2021BBC1BBC1N/AN/A
14ABCN/A2ABC21/1/190011/30/2022
15ABCN/A2ABC26/24/20221/1/3333
16BBC6001BBC112/1/202112/23/2021
17BBC7001BBC112/24/20211/5/2022
18BBC6001BBC11/6/20223/31/2022
19BBC7001BBC14/1/20224/26/2022
20BBC6001BBC14/27/20226/23/2022
21BBCN/A1BBC11/1/190011/30/2022
22BBCN/A1BBC16/24/20221/1/3333
23BBC8002BBC212/1/202112/23/2021
24BBC9002BBC212/24/20211/5/2022
25BBC6002BBC21/6/20223/31/2022
26BBC7002BBC24/1/20224/26/2022
27BBC3002BBC24/27/20226/23/2022
28BBCN/A2BBC21/1/190011/30/2022
29BBCN/A2BBC26/24/20221/1/3333
30
Sheet1
Cell Formulas
RangeFormula
M5M5=TEXTJOIN(,TRUE,K5,L5)
O5O5=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:P13P5=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:M13M6=TEXTJOIN(,TRUE,K6,L6)
O6:O13O6=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"))))
D2:D29D2=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
mjmax said:
Thanks but that defeats the entire point of the formula. The dates in column J will rarely match the dates in E & F. The formula below looks at the date in J5 and finds the Row where the date J5 would exist between the date in E and F. I had forgotten to add the dates that don't exist to the data table. This allows the formula to find the correct date in between E&F.


I'm afraid I didn't provide a good enough example. In the example below I show how I use this formula combine with IF function to pull the correct rate based on the Date and Rate Code. I'm thinking I can somehow combine the formula above with the INDEX match formula that looks for multiple criteria in order to perform this action without setting the IF statement to look for the Rate code.

This is the long crazy formula I am using currently:
Excel Formula: 
=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"))))

View attachment 47571

Here is the updated example. i hope this makes it more clear.

Example.xlsx
ABCDEFGHIJKLMOP
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC112/1/202112/23/2021
3ABC2001ABC112/24/20211/5/2022
4ABC1001ABC11/6/20223/31/2022DateHotelRoomRateCodeLooking for a easier formula to do this
5ABC2001ABC14/1/20224/26/20222/28/2022ABC2ABC2300
6ABC1001ABC14/27/20226/23/20227/8/2022BBC1BBC1N/A
7ABCN/A1ABC11/1/190011/30/202210/15/2022ABC1ABC1100
8ABCN/A1ABC16/24/202251/1/33337/5/2022ABC2ABC2300
9ABC3002ABC212/1/202112/23/20216/5/2022BBC1BBC1600
10ABC4002ABC212/24/20211/5/202212/26/2021ABC1ABC1200
11ABC3002ABC21/6/20223/31/20225/25/2021ABC2ABC2300
12ABC4002ABC24/1/20224/26/20226/18/2021BBC2BBC2N/A
13ABC3002ABC24/27/20226/23/20224/11/2021BBC1BBC1N/A
14ABCN/A2ABC21/1/190011/30/2022
15ABCN/A2ABC26/24/202251/1/3333
16BBC6001BBC112/1/202112/23/2021
17BBC7001BBC112/24/20211/5/2022
18BBC6001BBC11/6/20223/31/2022
19BBC7001BBC14/1/20224/26/2022
20BBC6001BBC14/27/20226/23/2022
21BBCN/A1BBC11/1/190011/30/2022
22BBCN/A1BBC16/24/202251/1/3333
23BBC8002BBC212/1/202112/23/2021
24BBC9002BBC212/24/20211/5/2022
25BBC6002BBC21/6/20223/31/2022
26BBC7002BBC24/1/20224/26/2022
27BBC3002BBC24/27/20226/23/2022
28BBCN/A2BBC21/1/190011/30/2022
29BBCN/A2BBC26/24/202251/1/3333
30
31
Sheet1
Cell Formulas
RangeFormula
M5M5=TEXTJOIN(,TRUE,K5,L5)
O5O5=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"))))
M6:M13M6=TEXTJOIN(,TRUE,K6,L6)
O6:O13O6=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"))))
D2:D29D2=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
I'm sorry. I found a typo in this example. Please see correction.
Example.xlsx
ABCDEFGHIJKLMO
1HotelRateRoomRateCodeStart DateEnd Date
2ABC1001ABC112/1/202112/23/2021
3ABC2001ABC112/24/20211/5/2022
4ABC1001ABC11/6/20223/31/2022DateHotelRoomRateCodeLooking for a easier formula to do this
5ABC2001ABC14/1/20224/26/20222/28/2022ABC2ABC2300
6ABC1001ABC14/27/20226/23/20227/8/2022BBC1BBC1N/A
7ABCN/A1ABC11/1/190011/30/202210/15/2022ABC1ABC1100
8ABCN/A1ABC16/24/20221/1/33337/5/2022ABC2ABC2300
9ABC3002ABC212/1/202112/23/20216/5/2022BBC1BBC1600
10ABC4002ABC212/24/20211/5/202212/26/2021ABC1ABC1200
11ABC3002ABC21/6/20223/31/20225/25/2021ABC2ABC2300
12ABC4002ABC24/1/20224/26/20226/18/2021BBC2BBC2N/A
13ABC3002ABC24/27/20226/23/20224/11/2021BBC1BBC1N/A
14ABCN/A2ABC21/1/190011/30/2022
15ABCN/A2ABC26/24/20221/1/3333
16BBC6001BBC112/1/202112/23/2021
17BBC7001BBC112/24/20211/5/2022
18BBC6001BBC11/6/20223/31/2022
19BBC7001BBC14/1/20224/26/2022
20BBC6001BBC14/27/20226/23/2022
21BBCN/A1BBC11/1/190011/30/2022
22BBCN/A1BBC16/24/20221/1/3333
23BBC8002BBC212/1/202112/23/2021
24BBC9002BBC212/24/20211/5/2022
25BBC6002BBC21/6/20223/31/2022
26BBC7002BBC24/1/20224/26/2022
27BBC3002BBC24/27/20226/23/2022
28BBCN/A2BBC21/1/190011/30/2022
29BBCN/A2BBC26/24/20221/1/3333
30
31
Sheet1
Cell Formulas
RangeFormula
M5M5=TEXTJOIN(,TRUE,K5,L5)
O5O5=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"))))
M6:M13M6=TEXTJOIN(,TRUE,K6,L6)
O6:O13O6=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"))))
D2:D29D2=TEXTJOIN(,TRUE,A2,C2,)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Some of those dates in E & F overlap, so which one should the formula return?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
318
etaf
etaf
C
  • Question Question
Nearest date based on three criteria
Replies
2
Views
400
chris_bosten
C
K
  • Question Question
Index+Match
Replies
4
Views
307
etaf
etaf
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
340
arendberg
A

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top