RETURN a DATE from one worksheet in between a DATE range with serial number as unique identifier

J

Jimbo Jones

New Member
Joined
May 12, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
I need a formula to return the Maintenance Date(Column B)(Maintenance Date Worksheet) to the Actual Ship & Return Dates Worksheet in Column C. Basically the Asset has a serial number; it is shipped to the worksite then returned to the base, maintenance is performed, then the serialized asset is returned to the worksite. The transactions are done in two different computer systems which is why i need to join the date ranges to make sure Maintenance is being done between the actual ship date and the return date. The unique identifier is the serial number column on both worksheets.

MR EXCEL WORKBOOK.xlsx
ABCD
1Lot Serial NumberActual Ship DateMaintenance DateReturn Date
56CEM01011/24/202011/29/2020
57CEM01011/24/202011/26/2020
58CEM01011/24/202011/26/2020
59CEM01110/3/202010/5/2020
60CEM01110/9/202010/20/2020
61CEM01110/18/202010/21/2020
62CEM01110/19/202010/23/2020
63CEM01110/24/202010/25/2020
64CEM01110/25/202011/1/2020
65CEM01110/25/202010/30/2020
66CEM01110/25/202010/30/2020
67CEM01110/28/202011/3/2020
68CEM01111/1/202011/2/2020
69CEM01111/2/202011/9/2020
70CEM01111/5/202011/9/2020
71CEM01111/5/202011/7/2020
72CEM01111/10/202011/12/2020
73CEM01410/18/202010/22/2020
74CEM01410/28/202011/8/2020
75CEM01411/12/202011/20/2020
76CEM01411/24/202012/3/2020
77CEM01412/4/202012/6/2020
78CEM01412/11/202012/15/2020
79CEM01412/15/202012/24/2020
80CEM0141/2/20211/5/2021
81CEM0142/15/20212/21/2021
82CEM0143/14/20213/14/2021
83CEM0143/19/20213/22/2021
84CEM0144/5/20214/15/2021
85CEM01811/12/202011/14/2020
86CEM01811/15/202011/16/2020
87CEM01811/17/202011/18/2020
88CEM0181/3/20211/9/2021
89CEM0181/4/20211/7/2021
90CEM0181/6/20211/9/2021
91CEM0181/6/20211/9/2021
92CEM0181/20/20211/31/2021
93CEM0181/31/20211/31/2021
94CEM0182/1/20212/19/2021
95CEM0182/1/20212/8/2021
96CEM0182/3/20212/11/2021
97CEM0182/7/20212/11/2021
98CEM0182/14/20212/17/2021
99CEM0182/14/20212/16/2021
100CEM0182/14/20212/15/2021
101CEM0182/21/20212/28/2021
102CEM0182/21/20212/24/2021
103CEM0182/21/20212/22/2021
104CEM0183/3/20213/6/2021
105CEM0183/7/20213/11/2021
106CEM0183/7/20213/10/2021
107CEM0183/7/20213/10/2021
108CEM0183/7/20213/7/2021
109CEM0183/7/20213/7/2021
Actual Ship & Return Dates



MR EXCEL WORKBOOK.xlsx
AB
1Serial NumMaintenance Date
11CEM00111/27/2020
12CEM00111/28/2020
13CEM00111/30/2020
14CEM00611/24/2020
15CEM00611/25/2020
16CEM00711/24/2020
17CEM00711/27/2020
18CEM00711/30/2020
19CEM0107/18/2020
20CEM0109/8/2020
21CEM01010/2/2020
22CEM01010/20/2020
23CEM01011/20/2020
24CEM01011/27/2020
25CEM0119/23/2020
26CEM01111/22/2020
27CEM0114/20/2021
28CEM0148/4/2020
29CEM0149/25/2020
30CEM0149/27/2020
31CEM0149/28/2020
32CEM0149/28/2020
33CEM0149/28/2020
34CEM0149/29/2020
35CEM01410/6/2020
36CEM01411/1/2020
37CEM01411/10/2020
38CEM01411/11/2020
39CEM01411/22/2020
40CEM01411/24/2020
41CEM01412/9/2020
42CEM01412/11/2020
43CEM0141/5/2021
44CEM0141/23/2021
45CEM0141/30/2021
46CEM0142/14/2021
47CEM0142/14/2021
48CEM0142/19/2021
49CEM0142/21/2021
50CEM0186/26/2020
51CEM0186/29/2020
52CEM0189/6/2020
53CEM0189/6/2020
54CEM0189/16/2020
55CEM0189/24/2020
56CEM0189/26/2020
57CEM01810/20/2020
58CEM01811/20/2020
59CEM01812/6/2020
60CEM01812/8/2020
61CEM01812/26/2020
62CEM01812/26/2020
63CEM01812/26/2020
64CEM01812/26/2020
65CEM0181/4/2021
66CEM0181/13/2021
67CEM0181/22/2021
68CEM0181/31/2021
69CEM0182/5/2021
70CEM0182/11/2021
71CEM0182/13/2021
72CEM0182/24/2021
73CEM0183/7/2021
74CEM0183/13/2021
75CEM0183/31/2021
76CEM0184/7/2021
77CEM0184/24/2021
78CEM0184/25/2021
79CEM0184/25/2021
80CEM0184/26/2021
MAINTENANCE DATE
 
Sort by date Sort by votes
Fluff said:
Without being able to see your actual data, there's not much I can do.
Click to expand...
Adoption Percentage REport WLS - 1_1_2021 through 5_20_2021.xlsx
MNOPQ
1Lot Serial NumberActual Ship DateAIRT CREATION DATEReturn DateAIRT#
215127852/6/20211/0/19003/26/20210
315127862/6/20211/0/19003/26/20210
4W-TTB-0009032/9/20211/0/19002/12/20210
513931282/15/20211/0/19003/4/20210
687627729-22/15/20211/0/19003/4/20210
7452/17/20211/0/19003/6/20210
86009762/17/20211/0/19002/19/20210
98391802/18/20211/0/19003/3/20210
1016026192/18/20211/0/19003/3/20210
1120041012/18/20211/0/19003/3/20210
1253595972/18/20211/0/19003/3/20210
1359007892/18/20211/0/19003/3/20210
1451912448-TT-T12/18/20211/0/19003/3/20210
15WC13062/18/20211/0/19003/3/20210
16WGTT2092/18/20211/0/19003/3/20210
178391802/23/20211/0/19003/29/20210
1812444092/23/20211/0/19003/29/20210
1913277872/23/20211/0/19003/29/20210
2013277932/23/20211/0/19003/29/20210
2115868632/23/20211/0/19003/29/20210
2220041012/23/20211/0/19003/29/20210
2351226972/23/20211/0/19003/29/20210
2453595972/23/20211/0/19003/29/20210
2556293682/23/20211/0/19003/29/20210
2659007892/23/20211/0/19003/29/20210
2713914338-62/23/20211/0/19003/29/20210
28278-ECTD-22/23/20211/0/19003/29/20210
29SSA32372/23/20211/0/19003/29/20210
30TTROM00692/23/20211/0/19003/29/20210
31WC13062/23/20211/0/19003/29/20210
32WGTT2092/23/20211/0/19003/29/20210
33WGTT62/23/20211/0/19003/29/20210
342870732/28/20211/0/19003/6/20210
352741453/2/20211/0/19004/8/20210
362125ARS0043/2/20211/0/19004/8/20210
37W-TTB-0119193/2/20211/0/19003/11/20210
38213/3/20211/0/19004/8/20210
39W-TTB-0004173/3/20211/0/19004/8/20210
40W-TTB-0009003/3/20211/0/19004/8/20210
41W-TTB-0143183/3/20211/0/19004/8/20210
422973373/5/20211/0/19003/24/20210
JDE DT DATA
Cell Formulas
RangeFormula
O2:O3O2=INDEX(FILTER('AIRT DATA'!B2001:B4581,('AIRT DATA'!$C$2:$C$2582=M2)*('AIRT DATA'!$B$2:$B$2582>=N2)*('AIRT DATA'!$B$2:$B$2582<=P2),""),1)
O4O4=INDEX(FILTER('AIRT DATA'!B2250:B4830,('AIRT DATA'!$C$2:$C$2582=M4)*('AIRT DATA'!$B$2:$B$2582>=N4)*('AIRT DATA'!$B$2:$B$2582<=P4),""),1)
O5O5=INDEX(FILTER('AIRT DATA'!B2549:B5129,('AIRT DATA'!$C$2:$C$2582=M5)*('AIRT DATA'!$B$2:$B$2582>=N5)*('AIRT DATA'!$B$2:$B$2582<=P5),""),1)
O6O6=INDEX(FILTER('AIRT DATA'!B2617:B5197,('AIRT DATA'!$C$2:$C$2582=M6)*('AIRT DATA'!$B$2:$B$2582>=N6)*('AIRT DATA'!$B$2:$B$2582<=P6),""),1)
O7O7=INDEX(FILTER('AIRT DATA'!B2684:B5264,('AIRT DATA'!$C$2:$C$2582=M7)*('AIRT DATA'!$B$2:$B$2582>=N7)*('AIRT DATA'!$B$2:$B$2582<=P7),""),1)
O8O8=INDEX(FILTER('AIRT DATA'!B2695:B5275,('AIRT DATA'!$C$2:$C$2582=M8)*('AIRT DATA'!$B$2:$B$2582>=N8)*('AIRT DATA'!$B$2:$B$2582<=P8),""),1)
O9O9=INDEX(FILTER('AIRT DATA'!B2748:B5328,('AIRT DATA'!$C$2:$C$2582=M9)*('AIRT DATA'!$B$2:$B$2582>=N9)*('AIRT DATA'!$B$2:$B$2582<=P9),""),1)
O10O10=INDEX(FILTER('AIRT DATA'!B2751:B5331,('AIRT DATA'!$C$2:$C$2582=M10)*('AIRT DATA'!$B$2:$B$2582>=N10)*('AIRT DATA'!$B$2:$B$2582<=P10),""),1)
O11O11=INDEX(FILTER('AIRT DATA'!B2755:B5335,('AIRT DATA'!$C$2:$C$2582=M11)*('AIRT DATA'!$B$2:$B$2582>=N11)*('AIRT DATA'!$B$2:$B$2582<=P11),""),1)
O12:O13O12=INDEX(FILTER('AIRT DATA'!B2766:B5346,('AIRT DATA'!$C$2:$C$2582=M12)*('AIRT DATA'!$B$2:$B$2582>=N12)*('AIRT DATA'!$B$2:$B$2582<=P12),""),1)
O14O14=INDEX(FILTER('AIRT DATA'!B2771:B5351,('AIRT DATA'!$C$2:$C$2582=M14)*('AIRT DATA'!$B$2:$B$2582>=N14)*('AIRT DATA'!$B$2:$B$2582<=P14),""),1)
O15:O16O15=INDEX(FILTER('AIRT DATA'!B2778:B5358,('AIRT DATA'!$C$2:$C$2582=M15)*('AIRT DATA'!$B$2:$B$2582>=N15)*('AIRT DATA'!$B$2:$B$2582<=P15),""),1)
O17O17=INDEX(FILTER('AIRT DATA'!B2935:B5515,('AIRT DATA'!$C$2:$C$2582=M17)*('AIRT DATA'!$B$2:$B$2582>=N17)*('AIRT DATA'!$B$2:$B$2582<=P17),""),1)
O18:O20O18=INDEX(FILTER('AIRT DATA'!B2941:B5521,('AIRT DATA'!$C$2:$C$2582=M18)*('AIRT DATA'!$B$2:$B$2582>=N18)*('AIRT DATA'!$B$2:$B$2582<=P18),""),1)
O21O21=INDEX(FILTER('AIRT DATA'!B2945:B5525,('AIRT DATA'!$C$2:$C$2582=M21)*('AIRT DATA'!$B$2:$B$2582>=N21)*('AIRT DATA'!$B$2:$B$2582<=P21),""),1)
O22O22=INDEX(FILTER('AIRT DATA'!B2947:B5527,('AIRT DATA'!$C$2:$C$2582=M22)*('AIRT DATA'!$B$2:$B$2582>=N22)*('AIRT DATA'!$B$2:$B$2582<=P22),""),1)
O23O23=INDEX(FILTER('AIRT DATA'!B2959:B5539,('AIRT DATA'!$C$2:$C$2582=M23)*('AIRT DATA'!$B$2:$B$2582>=N23)*('AIRT DATA'!$B$2:$B$2582<=P23),""),1)
O24:O25O24=INDEX(FILTER('AIRT DATA'!B2963:B5543,('AIRT DATA'!$C$2:$C$2582=M24)*('AIRT DATA'!$B$2:$B$2582>=N24)*('AIRT DATA'!$B$2:$B$2582<=P24),""),1)
O26O26=INDEX(FILTER('AIRT DATA'!B2967:B5547,('AIRT DATA'!$C$2:$C$2582=M26)*('AIRT DATA'!$B$2:$B$2582>=N26)*('AIRT DATA'!$B$2:$B$2582<=P26),""),1)
O27:O28O27=INDEX(FILTER('AIRT DATA'!B2970:B5550,('AIRT DATA'!$C$2:$C$2582=M27)*('AIRT DATA'!$B$2:$B$2582>=N27)*('AIRT DATA'!$B$2:$B$2582<=P27),""),1)
O29:O33O29=INDEX(FILTER('AIRT DATA'!B2973:B5553,('AIRT DATA'!$C$2:$C$2582=M29)*('AIRT DATA'!$B$2:$B$2582>=N29)*('AIRT DATA'!$B$2:$B$2582<=P29),""),1)
O34O34=INDEX(FILTER('AIRT DATA'!B3108:B5688,('AIRT DATA'!$C$2:$C$2582=M34)*('AIRT DATA'!$B$2:$B$2582>=N34)*('AIRT DATA'!$B$2:$B$2582<=P34),""),1)
O35O35=INDEX(FILTER('AIRT DATA'!B3250:B5830,('AIRT DATA'!$C$2:$C$2582=M35)*('AIRT DATA'!$B$2:$B$2582>=N35)*('AIRT DATA'!$B$2:$B$2582<=P35),""),1)
O36O36=INDEX(FILTER('AIRT DATA'!B3280:B5860,('AIRT DATA'!$C$2:$C$2582=M36)*('AIRT DATA'!$B$2:$B$2582>=N36)*('AIRT DATA'!$B$2:$B$2582<=P36),""),1)
O37O37=INDEX(FILTER('AIRT DATA'!B3286:B5866,('AIRT DATA'!$C$2:$C$2582=M37)*('AIRT DATA'!$B$2:$B$2582>=N37)*('AIRT DATA'!$B$2:$B$2582<=P37),""),1)
O38O38=INDEX(FILTER('AIRT DATA'!B3293:B5873,('AIRT DATA'!$C$2:$C$2582=M38)*('AIRT DATA'!$B$2:$B$2582>=N38)*('AIRT DATA'!$B$2:$B$2582<=P38),""),1)
O39:O40O39=INDEX(FILTER('AIRT DATA'!B3333:B5913,('AIRT DATA'!$C$2:$C$2582=M39)*('AIRT DATA'!$B$2:$B$2582>=N39)*('AIRT DATA'!$B$2:$B$2582<=P39),""),1)
O41O41=INDEX(FILTER('AIRT DATA'!B3338:B5918,('AIRT DATA'!$C$2:$C$2582=M41)*('AIRT DATA'!$B$2:$B$2582>=N41)*('AIRT DATA'!$B$2:$B$2582<=P41),""),1)
O42O42=INDEX(FILTER('AIRT DATA'!B3465:B6045,('AIRT DATA'!$C$2:$C$2582=M42)*('AIRT DATA'!$B$2:$B$2582>=N42)*('AIRT DATA'!$B$2:$B$2582<=P42),""),1)
Q2:Q3Q2=INDEX(FILTER('AIRT DATA'!A2001:A4581,('AIRT DATA'!$C$2:$C$2582=M2)*('AIRT DATA'!$B$2:$B$2582>=N2)*('AIRT DATA'!$B$2:$B$2582<=P2),""),1)
Q4Q4=INDEX(FILTER('AIRT DATA'!A2250:A4830,('AIRT DATA'!$C$2:$C$2582=M4)*('AIRT DATA'!$B$2:$B$2582>=N4)*('AIRT DATA'!$B$2:$B$2582<=P4),""),1)
Q5Q5=INDEX(FILTER('AIRT DATA'!A2549:A5129,('AIRT DATA'!$C$2:$C$2582=M5)*('AIRT DATA'!$B$2:$B$2582>=N5)*('AIRT DATA'!$B$2:$B$2582<=P5),""),1)
Q6Q6=INDEX(FILTER('AIRT DATA'!A2617:A5197,('AIRT DATA'!$C$2:$C$2582=M6)*('AIRT DATA'!$B$2:$B$2582>=N6)*('AIRT DATA'!$B$2:$B$2582<=P6),""),1)
Q7Q7=INDEX(FILTER('AIRT DATA'!A2684:A5264,('AIRT DATA'!$C$2:$C$2582=M7)*('AIRT DATA'!$B$2:$B$2582>=N7)*('AIRT DATA'!$B$2:$B$2582<=P7),""),1)
Q8Q8=INDEX(FILTER('AIRT DATA'!A2695:A5275,('AIRT DATA'!$C$2:$C$2582=M8)*('AIRT DATA'!$B$2:$B$2582>=N8)*('AIRT DATA'!$B$2:$B$2582<=P8),""),1)
Q9Q9=INDEX(FILTER('AIRT DATA'!A2748:A5328,('AIRT DATA'!$C$2:$C$2582=M9)*('AIRT DATA'!$B$2:$B$2582>=N9)*('AIRT DATA'!$B$2:$B$2582<=P9),""),1)
Q10Q10=INDEX(FILTER('AIRT DATA'!A2751:A5331,('AIRT DATA'!$C$2:$C$2582=M10)*('AIRT DATA'!$B$2:$B$2582>=N10)*('AIRT DATA'!$B$2:$B$2582<=P10),""),1)
Q11Q11=INDEX(FILTER('AIRT DATA'!A2755:A5335,('AIRT DATA'!$C$2:$C$2582=M11)*('AIRT DATA'!$B$2:$B$2582>=N11)*('AIRT DATA'!$B$2:$B$2582<=P11),""),1)
Q12:Q13Q12=INDEX(FILTER('AIRT DATA'!A2766:A5346,('AIRT DATA'!$C$2:$C$2582=M12)*('AIRT DATA'!$B$2:$B$2582>=N12)*('AIRT DATA'!$B$2:$B$2582<=P12),""),1)
Q14Q14=INDEX(FILTER('AIRT DATA'!A2771:A5351,('AIRT DATA'!$C$2:$C$2582=M14)*('AIRT DATA'!$B$2:$B$2582>=N14)*('AIRT DATA'!$B$2:$B$2582<=P14),""),1)
Q15:Q16Q15=INDEX(FILTER('AIRT DATA'!A2778:A5358,('AIRT DATA'!$C$2:$C$2582=M15)*('AIRT DATA'!$B$2:$B$2582>=N15)*('AIRT DATA'!$B$2:$B$2582<=P15),""),1)
Q17Q17=INDEX(FILTER('AIRT DATA'!A2935:A5515,('AIRT DATA'!$C$2:$C$2582=M17)*('AIRT DATA'!$B$2:$B$2582>=N17)*('AIRT DATA'!$B$2:$B$2582<=P17),""),1)
Q18:Q20Q18=INDEX(FILTER('AIRT DATA'!A2941:A5521,('AIRT DATA'!$C$2:$C$2582=M18)*('AIRT DATA'!$B$2:$B$2582>=N18)*('AIRT DATA'!$B$2:$B$2582<=P18),""),1)
Q21Q21=INDEX(FILTER('AIRT DATA'!A2945:A5525,('AIRT DATA'!$C$2:$C$2582=M21)*('AIRT DATA'!$B$2:$B$2582>=N21)*('AIRT DATA'!$B$2:$B$2582<=P21),""),1)
Q22Q22=INDEX(FILTER('AIRT DATA'!A2947:A5527,('AIRT DATA'!$C$2:$C$2582=M22)*('AIRT DATA'!$B$2:$B$2582>=N22)*('AIRT DATA'!$B$2:$B$2582<=P22),""),1)
Q23Q23=INDEX(FILTER('AIRT DATA'!A2959:A5539,('AIRT DATA'!$C$2:$C$2582=M23)*('AIRT DATA'!$B$2:$B$2582>=N23)*('AIRT DATA'!$B$2:$B$2582<=P23),""),1)
Q24:Q25Q24=INDEX(FILTER('AIRT DATA'!A2963:A5543,('AIRT DATA'!$C$2:$C$2582=M24)*('AIRT DATA'!$B$2:$B$2582>=N24)*('AIRT DATA'!$B$2:$B$2582<=P24),""),1)
Q26Q26=INDEX(FILTER('AIRT DATA'!A2967:A5547,('AIRT DATA'!$C$2:$C$2582=M26)*('AIRT DATA'!$B$2:$B$2582>=N26)*('AIRT DATA'!$B$2:$B$2582<=P26),""),1)
Q27:Q28Q27=INDEX(FILTER('AIRT DATA'!A2970:A5550,('AIRT DATA'!$C$2:$C$2582=M27)*('AIRT DATA'!$B$2:$B$2582>=N27)*('AIRT DATA'!$B$2:$B$2582<=P27),""),1)
Q29:Q33Q29=INDEX(FILTER('AIRT DATA'!A2973:A5553,('AIRT DATA'!$C$2:$C$2582=M29)*('AIRT DATA'!$B$2:$B$2582>=N29)*('AIRT DATA'!$B$2:$B$2582<=P29),""),1)
Q34Q34=INDEX(FILTER('AIRT DATA'!A3108:A5688,('AIRT DATA'!$C$2:$C$2582=M34)*('AIRT DATA'!$B$2:$B$2582>=N34)*('AIRT DATA'!$B$2:$B$2582<=P34),""),1)
Q35Q35=INDEX(FILTER('AIRT DATA'!A3250:A5830,('AIRT DATA'!$C$2:$C$2582=M35)*('AIRT DATA'!$B$2:$B$2582>=N35)*('AIRT DATA'!$B$2:$B$2582<=P35),""),1)
Q36Q36=INDEX(FILTER('AIRT DATA'!A3280:A5860,('AIRT DATA'!$C$2:$C$2582=M36)*('AIRT DATA'!$B$2:$B$2582>=N36)*('AIRT DATA'!$B$2:$B$2582<=P36),""),1)
Q37Q37=INDEX(FILTER('AIRT DATA'!A3286:A5866,('AIRT DATA'!$C$2:$C$2582=M37)*('AIRT DATA'!$B$2:$B$2582>=N37)*('AIRT DATA'!$B$2:$B$2582<=P37),""),1)
Q38Q38=INDEX(FILTER('AIRT DATA'!A3293:A5873,('AIRT DATA'!$C$2:$C$2582=M38)*('AIRT DATA'!$B$2:$B$2582>=N38)*('AIRT DATA'!$B$2:$B$2582<=P38),""),1)
Q39:Q40Q39=INDEX(FILTER('AIRT DATA'!A3333:A5913,('AIRT DATA'!$C$2:$C$2582=M39)*('AIRT DATA'!$B$2:$B$2582>=N39)*('AIRT DATA'!$B$2:$B$2582<=P39),""),1)
Q41Q41=INDEX(FILTER('AIRT DATA'!A3338:A5918,('AIRT DATA'!$C$2:$C$2582=M41)*('AIRT DATA'!$B$2:$B$2582>=N41)*('AIRT DATA'!$B$2:$B$2582<=P41),""),1)
Q42Q42=INDEX(FILTER('AIRT DATA'!A3465:A6045,('AIRT DATA'!$C$2:$C$2582=M42)*('AIRT DATA'!$B$2:$B$2582>=N42)*('AIRT DATA'!$B$2:$B$2582<=P42),""),1)



Adoption Percentage REport WLS - 1_1_2021 through 5_20_2021.xlsx
ABCD
1ART NumCreate DateSerial Num
213266216367-ART3/11/2021151278513266215473-ART
313266216371-ART3/11/2021151278613266215475-ART
413266216392-ART3/11/2021777170
513266216393-ART3/11/202188222424-20
6101592121542-ART3/13/2021WGTT60
710250211564-ART3/13/202122664890
810250211566-ART3/13/202190842112-10
9110200213114-ART3/14/2021RD63750
10110200213115-ART3/14/2021RD 52590
11110200213116-ART3/14/202197052711-20
12110200213117-ART3/14/202119-1444-13882-10
13110200213118-ART3/14/20212994750
14110200213119-ART3/14/202198362252-10
15110200213120-ART3/14/202198362252-20
16836332110685-ART3/15/2021W-TTB-0009030
17110200213153-ART3/15/202197936795-20
18110200213156-ART3/15/202198422403-10
19101592121584-ART3/16/202113914338-60
2013266216509-ART3/16/20212958150
2113266216542-ART3/17/202123253140
2213266216543-ART3/17/202112684150
2310250211582-ART3/18/20212973360
2410250211583-ART3/18/20217365320
2510250211584-ART3/18/20217365320
2613266216576-ART3/18/202115730330
27110200213223-ART3/19/2021RD63750
28110200213224-ART3/19/2021RD 52590
29110200213230-ART3/20/202197052711-20
30110200213235-ART3/20/202119-1444-13882-10
3113266216719-ART3/22/2021BPC4A41160
32836332110840-ART3/23/2021450
33110200213346-ART3/26/202198362252-20
34110200213347-ART3/26/202198362252-10
35110200213348-ART3/26/20212994750
36101592121891-ART3/28/2021SSA32370
3710250211663-ART3/29/20217365100
3810250211665-ART3/29/20212952330
39101512111021-ART3/29/202168339246-80
4040069212494-ART3/31/20213005170
4140069212495-ART3/31/202117141570
4240069212496-ART3/31/202166084235-40
4340069212497-ART3/31/202166084235-40
4440069212498-ART3/31/2021MRL/2018003951-010
4540069212499-ART3/31/2021MRL/2018003951-020
4640069212502-ART3/31/202191981093-20
4740069212503-ART3/31/202191981093-10
4810250211703-ART3/31/20217365320
4910250211705-ART3/31/20212973370
50110200213463-ART4/1/202198422403-10
51836332110929-ART4/1/2021450
5213266217032-ART4/1/20212540740
5313266217037-ART4/1/202119230310
54110200213479-ART4/1/202198424468-20
5513266217066-ART4/3/2021651162150
5613266217068-ART4/3/2021TTN-1312070
5713266217069-ART4/3/202133168800
5813266217087-ART4/4/2021TTN-1313010
5913266217091-ART4/4/2021662837620
6013266217092-ART4/4/202145ISOSB2010030
6113266217093-ART4/4/202112937660
6213266217094-ART4/4/202145ISOSB2010070
6313266217096-ART4/4/20212125SAD0050
6413266217097-ART4/4/202164969697-T1-TT0
6513266217098-ART4/4/20212125ARS0020
6613266217099-ART4/4/202145ISOMB2010040
6713266217101-ART4/4/202145ISOMB2010060
6813266217105-ART4/4/2021ADIES360010
6913266217106-ART4/4/2021ADIES36001A0
7013266217107-ART4/4/2021ADIES36001A0
7113266217108-ART4/4/2021ADIES36001B0
7213266217110-ART4/4/202110402450
7313266217111-ART4/4/202121493450
7413266217114-ART4/4/2021400FRGS0030
7513266217116-ART4/4/20212125CTS0010
7613266217117-ART4/4/20212747090
7713266217129-ART4/4/20212631360
7813266217132-ART4/4/20212613210
7913266217133-ART4/4/20212680460
8013266217149-ART4/4/2021151278613266215475-ART
8113266217150-ART4/4/202115AU0009AD0
8213266217154-ART4/4/202145801730
8313266217158-ART4/4/20213075830
8413266217188-ART4/5/20213026510
8513266217192-ART4/5/20213005180
8613266217206-ART4/5/202110310
8713266217207-ART4/5/202112684150
8813266217209-ART4/5/202151924094-70
8913266217210-ART4/5/202127375750
9013266217211-ART4/5/20212791750
9113266217219-ART4/5/20212125BN0020
9213266217234-ART4/6/2021BPC4A41160
9313266217241-ART4/6/202167109975-T1-TT0
9413266217253-ART4/6/20212125SAD0040
9513266217264-ART4/6/202135GS0010
9613266217287-ART4/7/20213015480
9710250211733-ART4/8/202122664890
9813266217368-ART4/11/202116124210
9913266217369-ART4/11/2021TTN-1309370
10013266217395-ART4/12/2021SSA13430
1011493721245-ART4/12/202171264231-10
1021493721246-ART4/13/202171264231-20
1031493721247-ART4/13/202114134093-60
104836332111110-ART4/15/202118890180
105836332111111-ART4/15/202114104200
106836332111113-ART4/15/2021W-TTB-0155260
107101592122428-ART4/19/202153595910
108101592122429-ART4/19/202153595970
10910250211819-ART4/19/20212870730
110101592122433-ART4/19/20212730340
111101592122436-ART4/20/202113914338-60
112110200213623-ART4/20/202198422403-10
113101592122441-ART4/21/202151581840
11410250211834-ART4/21/202122792270
11513266217655-ART4/21/202133168800
11613266217657-ART4/21/2021TTN-1313010
11713266217660-ART4/21/2021651162150
11813266217661-ART4/21/2021TTN-1312070
119110200213657-ART4/22/202198424468-20
12013266217675-ART4/22/202151310156-S2-TT0
121110200213681-ART4/24/202198422403-10
122110200213683-ART4/25/202197936795-20
12310250211856-ART4/26/20212973360
12440069212654-ART4/27/202191981093-20
12510250211860-ART4/28/20212958560
126101592122550-ART4/28/2021TTN-1297260
12713266217877-ART4/28/2021400FRGS0020
12813266217885-ART4/28/20212958730
1291493721249-ART4/29/2021705051010
13010250211874-ART4/29/202122792270
1311493721251-ART4/29/202152531997-10
132110200213716-ART4/29/202198422403-10
13313266217942-ART4/29/2021SSA13430
13413266217989-ART5/1/202135GS0010
13513266217990-ART5/1/20212125SAD0040
13613266217991-ART5/1/202167109975-T1-TT0
13713266217998-ART5/1/20212747090
13813266218001-ART5/1/202115AU0009AD0
13913266218004-ART5/1/20212631360
14013266218013-ART5/1/20213005180
141101592122627-ART5/2/202120041010
14213266218076-ART5/2/202127375750
14313266218077-ART5/2/202151924094-70
144110200213745-ART5/2/2021RD63750
145110200213746-ART5/2/2021RD 52590
146110200213747-ART5/2/202197936795-20
147110200213748-ART5/2/202197052711-20
148110200213752-ART5/2/202119-1444-13882-10
149110200213757-ART5/2/20212994750
150110200213758-ART5/2/202198362252-10
151110200213759-ART5/2/202198362252-20
15210250211889-ART5/3/202126007090
1531493721253-ART5/3/202153119030-10
15413266218186-ART5/4/202127375750
15510250211902-ART5/4/202123163440
15610250211903-ART5/4/202119573530
15713266218200-ART5/5/20212791750
15813266218201-ART5/5/202121493450
15913266218202-ART5/5/2021662837620
16013266218203-ART5/5/20212125CTS0010
16113266218204-ART5/5/202112937660
16213266218206-ART5/5/20212125SAD0050
16313266218207-ART5/5/20212613210
16413266218208-ART5/5/20212125ARS0020
16513266218212-ART5/5/20212680460
16613266218213-ART5/5/20212125BN0020
16713266218238-ART5/5/20212958730
16813266218251-ART5/5/202145801730
16913266218252-ART5/5/20213075830
17013266218276-ART5/5/20213075830
17113266218281-ART5/5/202112684150
17213266218282-ART5/5/20212613210
17313266218297-ART5/5/20212125CTS0010
17413266218300-ART5/5/20212125ARS0020
17513266218309-ART5/5/20212125SAD0040
17613266218310-ART5/5/202121493450
17713266218312-ART5/5/202167109975-T1-TT0
17813266218320-ART5/5/20212631360
17913266218326-ART5/5/20212747090
18013266218338-ART5/5/202112937660
18113266218341-ART5/5/20212125SAD0050
18240069212720-ART5/5/202133702290
183110200213799-ART5/6/202198424468-20
18413266218423-ART5/8/202119230310
18513266218430-ART5/8/202164969697-T1-TT0
18613266218514-ART5/9/2021400FRGS0030
187110200213832-ART5/14/202198424468-20
18813266218771-ART5/14/2021400FRGS0020
189110200213851-ART5/17/202198424468-20
190110200213852-ART5/17/202198424468-20
191110200213853-ART5/17/202198424468-20
192101592122965-ART5/17/202151581770
193101592122966-ART5/17/202151581840
194101592122967-ART5/17/2021WC13060
195101592123011-ART5/18/202151912448-TT-T10
19610250211926-ART5/18/20212973370
197110200213858-ART5/18/202198422403-10
19813266218993-ART5/19/202115730330
AIRT DATA
Cell Formulas
RangeFormula
D2:D198D2=XLOOKUP(C:C,'JDE DT DATA'!M:M,'JDE DT DATA'!Q:Q)
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I hope this makes sense to you Fluff. The Xlookup in sheet 'AIRT DATA' was just so i could narrow down the results as i wasnt allowed to post the full data set due to forum data limits.
 
Upvote 0
You filtering a different range from the range you are comparing to
Rich (BB code): 
=INDEX(FILTER('AIRT DATA'!B2001:B4581,('AIRT DATA'!$C$2:$C$2582=M2)*('AIRT DATA'!$B$2:$B$2582>=N2)*('AIRT DATA'!$B$2:$B$2582<=P2),""),1)
 
Upvote 0
I auto filled the formula in my JDE DT Data sheet... where i have 7177 rows.....
 
Upvote 0
The ranges need to be the same.
 
Upvote 0
I dont have that option..... can you help me with a work around? Is there a different way to approach this? My 'JDE DT DATA' is always a much longer list than my 'AIRT DATA'......
 
Upvote 0
Sorry but that makes no sense at all, it doesn't matter how much data you have on each sheet.
The ranges on the AIRT sheet MUST match or it will not work. Like
Excel Formula: 
=INDEX(FILTER('AIRT Data'!$B$2:$B$10000,('AIRT Data'!$C$2:$C$10000=M2)*('AIRT Data'!$B$2:$B$10000>=N2)*('AIRT Data'!$B$2:$B$10000<=P2),""),1)
 
Upvote 0
Fluff said:
Sorry but that makes no sense at all, it doesn't matter how much data you have on each sheet.
The ranges on the AIRT sheet MUST match or it will not work. Like
Excel Formula: 
=INDEX(FILTER('AIRT Data'!$B$2:$B$10000,('AIRT Data'!$C$2:$C$10000=M2)*('AIRT Data'!$B$2:$B$10000>=N2)*('AIRT Data'!$B$2:$B$10000<=P2),""),1)
Click to expand...
When i copy your formula into O2, then double click the bottom right hand corner of the cell to auto fill it down the sheet. The ranges remain the same except for the underlined portion below it auto fills into a numerical series.... which changes the range.....

Excel Formula:

=INDEX(FILTER('AIRT DATA'!B2:B2582,('AIRT DATA'!$C$2:$C$2582=M2)*('AIRT DATA'!$B$2:$B$2582>=N2)*('AIRT DATA'!$B$2:$B$2582<=P2),""),1)
=INDEX(FILTER('AIRT DATA'!B3:B2583,('AIRT DATA'!$C$2:$C$2582=M3)*('AIRT DATA'!$B$2:$B$2582>=N3)*('AIRT DATA'!$B$2:$B$2582<=P3),""),1)
=INDEX(FILTER('AIRT DATA'!B4:B2584,('AIRT DATA'!$C$2:$C$2582=M4)*('AIRT DATA'!$B$2:$B$2582>=N4)*('AIRT DATA'!$B$2:$B$2582<=P4),""),1)
=INDEX(FILTER('AIRT DATA'!B5:B2585,('AIRT DATA'!$C$2:$C$2582=M5)*('AIRT DATA'!$B$2:$B$2582>=N5)*('AIRT DATA'!$B$2:$B$2582<=P5),""),1)
=INDEX(FILTER('AIRT DATA'!B6:B2586,('AIRT DATA'!$C$2:$C$2582=M6)*('AIRT DATA'!$B$2:$B$2582>=N6)*('AIRT DATA'!$B$2:$B$2582<=P6),""),1)
=INDEX(FILTER('AIRT DATA'!B7:B2587,('AIRT DATA'!$C$2:$C$2582=M7)*('AIRT DATA'!$B$2:$B$2582>=N7)*('AIRT DATA'!$B$2:$B$2582<=P7),""),1)
=INDEX(FILTER('AIRT DATA'!B8:B2588,('AIRT DATA'!$C$2:$C$2582=M8)*('AIRT DATA'!$B$2:$B$2582>=N8)*('AIRT DATA'!$B$2:$B$2582<=P8),""),1)
=INDEX(FILTER('AIRT DATA'!B9:B2589,('AIRT DATA'!$C$2:$C$2582=M9)*('AIRT DATA'!$B$2:$B$2582>=N9)*('AIRT DATA'!$B$2:$B$2582<=P9),""),1)
=INDEX(FILTER('AIRT DATA'!B10:B2590,('AIRT DATA'!$C$2:$C$2582=M10)*('AIRT DATA'!$B$2:$B$2582>=N10)*('AIRT DATA'!$B$2:$B$2582<=P10),""),1)
=INDEX(FILTER('AIRT DATA'!B11:B2591,('AIRT DATA'!$C$2:$C$2582=M11)*('AIRT DATA'!$B$2:$B$2582>=N11)*('AIRT DATA'!$B$2:$B$2582<=P11),""),1)
=INDEX(FILTER('AIRT DATA'!B12:B2592,('AIRT DATA'!$C$2:$C$2582=M12)*('AIRT DATA'!$B$2:$B$2582>=N12)*('AIRT DATA'!$B$2:$B$2582<=P12),""),1)
=INDEX(FILTER('AIRT DATA'!B13:B2593,('AIRT DATA'!$C$2:$C$2582=M13)*('AIRT DATA'!$B$2:$B$2582>=N13)*('AIRT DATA'!$B$2:$B$2582<=P13),""),1)
=INDEX(FILTER('AIRT DATA'!B14:B2594,('AIRT DATA'!$C$2:$C$2582=M14)*('AIRT DATA'!$B$2:$B$2582>=N14)*('AIRT DATA'!$B$2:$B$2582<=P14),""),1)
=INDEX(FILTER('AIRT DATA'!B15:B2595,('AIRT DATA'!$C$2:$C$2582=M15)*('AIRT DATA'!$B$2:$B$2582>=N15)*('AIRT DATA'!$B$2:$B$2582<=P15),""),1)
=INDEX(FILTER('AIRT DATA'!B16:B2596,('AIRT DATA'!$C$2:$C$2582=M16)*('AIRT DATA'!$B$2:$B$2582>=N16)*('AIRT DATA'!$B$2:$B$2582<=P16),""),1)
=INDEX(FILTER('AIRT DATA'!B17:B2597,('AIRT DATA'!$C$2:$C$2582=M17)*('AIRT DATA'!$B$2:$B$2582>=N17)*('AIRT DATA'!$B$2:$B$2582<=P17),""),1)
=INDEX(FILTER('AIRT DATA'!B18:B2598,('AIRT DATA'!$C$2:$C$2582=M18)*('AIRT DATA'!$B$2:$B$2582>=N18)*('AIRT DATA'!$B$2:$B$2582<=P18),""),1)
=INDEX(FILTER('AIRT DATA'!B19:B2599,('AIRT DATA'!$C$2:$C$2582=M19)*('AIRT DATA'!$B$2:$B$2582>=N19)*('AIRT DATA'!$B$2:$B$2582<=P19),""),1)
=INDEX(FILTER('AIRT DATA'!B20:B2600,('AIRT DATA'!$C$2:$C$2582=M20)*('AIRT DATA'!$B$2:$B$2582>=N20)*('AIRT DATA'!$B$2:$B$2582<=P20),""),1)
=INDEX(FILTER('AIRT DATA'!B21:B2601,('AIRT DATA'!$C$2:$C$2582=M21)*('AIRT DATA'!$B$2:$B$2582>=N21)*('AIRT DATA'!$B$2:$B$2582<=P21),""),1)
=INDEX(FILTER('AIRT DATA'!B22:B2602,('AIRT DATA'!$C$2:$C$2582=M22)*('AIRT DATA'!$B$2:$B$2582>=N22)*('AIRT DATA'!$B$2:$B$2582<=P22),""),1)
=INDEX(FILTER('AIRT DATA'!B23:B2603,('AIRT DATA'!$C$2:$C$2582=M23)*('AIRT DATA'!$B$2:$B$2582>=N23)*('AIRT DATA'!$B$2:$B$2582<=P23),""),1)
=INDEX(FILTER('AIRT DATA'!B24:B2604,('AIRT DATA'!$C$2:$C$2582=M24)*('AIRT DATA'!$B$2:$B$2582>=N24)*('AIRT DATA'!$B$2:$B$2582<=P24),""),1)
=INDEX(FILTER('AIRT DATA'!B25:B2605,('AIRT DATA'!$C$2:$C$2582=M25)*('AIRT DATA'!$B$2:$B$2582>=N25)*('AIRT DATA'!$B$2:$B$2582<=P25),""),1)
=INDEX(FILTER('AIRT DATA'!B26:B2606,('AIRT DATA'!$C$2:$C$2582=M26)*('AIRT DATA'!$B$2:$B$2582>=N26)*('AIRT DATA'!$B$2:$B$2582<=P26),""),1)
=INDEX(FILTER('AIRT DATA'!B27:B2607,('AIRT DATA'!$C$2:$C$2582=M27)*('AIRT DATA'!$B$2:$B$2582>=N27)*('AIRT DATA'!$B$2:$B$2582<=P27),""),1)
=INDEX(FILTER('AIRT DATA'!B28:B2608,('AIRT DATA'!$C$2:$C$2582=M28)*('AIRT DATA'!$B$2:$B$2582>=N28)*('AIRT DATA'!$B$2:$B$2582<=P28),""),1)
=INDEX(FILTER('AIRT DATA'!B29:B2609,('AIRT DATA'!$C$2:$C$2582=M29)*('AIRT DATA'!$B$2:$B$2582>=N29)*('AIRT DATA'!$B$2:$B$2582<=P29),""),1)
 
Upvote 0
You have not copied my formula.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
Cubist
  • Solved
Return values based on date range
Replies
3
Views
154
Peter_SSs
Peter_SSs
F
  • Solved
Sort scripture references by chapter number
Replies
9
Views
223
Peter_SSs
Peter_SSs
S
  • Solved
lookup return from range of dates
Replies
4
Views
107
Peter_SSs
Peter_SSs
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
370
kevin9999
K

Forum statistics

Threads
1,214,968
Messages
6,122,506
Members
449,089
Latest member
RandomExceller01

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