How to Lookup a number within a range and return a value if that number is within that range

SwanB

New Member
Joined
Aug 31, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi there. I am hoping there is someone that can help me with a lookup, index or match formula. I have a spreadsheet with samples numbers. What I want is a formula to look for a sample number within a range in a table and return the new sample number. Example: If the value in A3 is within the range of F3:G31 then give me the new CompID H3:H31 that it goes with. If it does not then I would need it to say "DO NOT USE IN COMPOSITE".
I have a couple formulas that sort of work but for some reason not all the way.
Formula 1 =INDEX($H$3:$H$23,MATCH(1,($F$3:$F$23<=A3)*($G$3:$G$23>=A3),0),)
Formula 2 =LOOKUP(1,1/(($F$3:$F$23<=A25)*($G$3:$G$23>=A25)),$H$3:$H$23)

Any help would be much appreciated!
Thanks!

lookup.xlsx
C
26MZ22-01_100-120_COMP
Sheet1
Cell Formulas
RangeFormula
C26C26=LOOKUP(2,1/(($F$3:$F$23<=A26)*($G$3:$G$23>=A26)),$H$3:$H$23)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I don't think my attachment worked on my original post. Hopefully this one worked.
thanks

lookup.xlsx
ABCDEFGH
1Sample_IDNew_Composite_Sample_IDCompSTARTCompENDCompID
2MZ22-01_0B
3MZ22-01_0-5MZ22-01_0-20_COMPMZ22-01_0-5MZ22-01_15-20MZ22-01_0-20_COMP
4MZ22-01_5-10MZ22-01_20-25MZ22-01_35-40MZ22-01_20-40_COMP
5MZ22-01_10-15MZ22-01_0-20_COMPMZ22-01_40-45MZ22-01_50-55MZ22-01_40-55_COMP
6MZ22-01_15-20MZ22-01_0-20_COMPMZ22-01_55-60MZ22-01_65-70MZ22-01_55-70_COMP
7MZ22-01_20-25MZ22-01_20-40_COMPMZ22-01_70-75MZ22-01_80-85MZ22-01_70-85_COMP
8MZ22-01_25-30MZ22-01_20-40_COMPMZ22-01_85-90MZ22-01_95-100MZ22-01_85-100_COMP
9MZ22-01_30-35MZ22-01_20-40_COMPMZ22-01_100-105MZ22-01_115-120MZ22-01_100-120_COMP
10MZ22-01_35-40MZ22-01_20-40_COMPMZ22-01_120-125MZ22-01_135-140MZ22-01_120-140_COMP
11MZ22-01_40-45MZ22-01_40-55_COMPMZ22-01_140-145MZ22-01_155-160MZ22-01_140-160_COMP
12MZ22-01_45-50MZ22-01_40-55_COMPMZ22-01_160-165MZ22-01_175-180MZ22-01_160-180_COMP
13MZ22-01_45-50AMZ22-01_180-185MZ22-01_195-200MZ22-01_180-200_COMP
14MZ22-01_50-55MZ22-01_40-55_COMPMZ22-01_200-205MZ22-01_215-220MZ22-01_200-220_COMP
15MZ22-01_55-60MZ22-01_55-70_COMPMZ22-01_220-225MZ22-01_235-240MZ22-01_220-240_COMP
16MZ22-01_60-65MZ22-01_55-70_COMPMZ22-01_240-245MZ22-01_255-260MZ22-01_240-260_COMP
17MZ22-01_65-70MZ22-01_55-70_COMPMZ22-01_260-265MZ22-01_275-280MZ22-01_260-280_COMP
18MZ22-01_70-75MZ22-01_70-85_COMPMZ22-01_280-285MZ22-01_295-300MZ22-01_280-300_COMP
19MZ22-01_75-80MZ22-01_70-85_COMPMZ22-01_300-305MZ22-01_315-320MZ22-01_300-320_COMP
20MZ22-01_80-85MZ22-01_70-85_COMPMZ22-01_320-325MZ22-01_335-340MZ22-01_320-340_COMP
21MZ22-01_85-90MZ22-01_85-100_COMPMZ22-01_340-345MZ22-01_355-360MZ22-01_340-360_COMP
22MZ22-01_90-95MZ22-01_85-100_COMPMZ22-01_360-365MZ22-01_375-380MZ22-01_360-380_COMP
23MZ22-01_95-100MZ22-01_85-100_COMPMZ22-01_380-385MZ22-01_395-400MZ22-01_380-400_COMP
24MZ22-01_95-100C#N/AMZ22-01_400-405MZ22-01_415-420MZ22-01_400-420_COMP
25MZ22-01_100-105MZ22-01_100-120_COMPMZ22-01_420-425MZ22-01_435-440MZ22-01_420-440_COMP
26MZ22-01_105-110MZ22-01_100-120_COMPMZ22-01_440-445MZ22-01_455-460MZ22-01_440-460_COMP
27MZ22-01_110-115MZ22-01_100-120_COMPMZ22-01_460-465MZ22-01_475-480MZ22-01_460-480_COMP
28MZ22-01_115-120MZ22-01_100-120_COMPMZ22-01_480-485MZ22-01_495-500MZ22-01_480-500_COMP
29MZ22-01_120-125MZ22-01_120-140_COMPMZ22-01_500-505MZ22-01_515-520MZ22-01_500-520_COMP
30MZ22-01_125-130MZ22-01_120-140_COMPMZ22-01_520-525MZ22-01_535-540MZ22-01_520-540_COMP
31MZ22-01_130-135MZ22-01_120-140_COMPMZ22-01_540-545MZ22-01_555-560MZ22-01_540-560_COMP
32MZ22-01_135-140MZ22-01_120-140_COMP
33MZ22-01_140-145MZ22-01_140-160_COMP
34MZ22-01_145-150MZ22-01_140-160_COMP
35MZ22-01_140-145B
36MZ22-01_150-155MZ22-01_140-160_COMP
37MZ22-01_155-160MZ22-01_140-160_COMP
38MZ22-01_160-165MZ22-01_160-180_COMP
39MZ22-01_165-170MZ22-01_160-180_COMP
40MZ22-01_170-175MZ22-01_160-180_COMP
41MZ22-01_175-180MZ22-01_160-180_COMP
42MZ22-01_180-185MZ22-01_180-200_COMP
43MZ22-01_185-190MZ22-01_180-200_COMP
44MZ22-01_190-195MZ22-01_180-200_COMP
45MZ22-01_195-200MZ22-01_180-200_COMP
46MZ22-01_195-200A#N/A
47MZ22-01_200-205MZ22-01_200-220_COMP
48MZ22-01_205-210MZ22-01_200-220_COMP
49MZ22-01_210-215MZ22-01_200-220_COMP
50MZ22-01_215-220MZ22-01_200-220_COMP
51MZ22-01_220-225MZ22-01_220-240_COMP
52MZ22-01_225-230MZ22-01_220-240_COMP
53MZ22-01_230-235MZ22-01_220-240_COMP
54MZ22-01_235-240MZ22-01_220-240_COMP
Sheet1
Cell Formulas
RangeFormula
A2:A3,A5:A54A2='[Copy of MZ22-01_Log_FINAL.xlsx]Samples'!E2
C3,C14:C23,C5:C12C3=INDEX($H$3:$H$23,MATCH(1,($F$3:$F$23<=A3)*($G$3:$G$23>=A3),0),)
F3:G31F3='[Copy of MZ22-01_Log_FINAL.xlsx]Composites'!M2
H3:H31H3='[Copy of MZ22-01_Log_FINAL.xlsx]Composites'!E2
C24:C25,C37:C54C24=LOOKUP(1,1/(($F$3:$F$23<=A24)*($G$3:$G$23>=A24)),$H$3:$H$23)
C26:C34,C36C26=LOOKUP(2,1/(($F$3:$F$23<=A26)*($G$3:$G$23>=A26)),$H$3:$H$23)
 
Upvote 0
Perhaps this:

Book1
ABCDEFGH
1Sample_IDNew_Composite_Sample_IDCompSTARTCompENDCompID
2MZ22-01_0BDO NOT USE IN COMPOSITE
3MZ22-01_0-5MZ22-01_0-20_COMPMZ22-01_0-5MZ22-01_15-20MZ22-01_0-20_COMP
4MZ22-01_5-10DO NOT USE IN COMPOSITEMZ22-01_20-25MZ22-01_35-40MZ22-01_20-40_COMP
5MZ22-01_10-15DO NOT USE IN COMPOSITEMZ22-01_40-45MZ22-01_50-55MZ22-01_40-55_COMP
6MZ22-01_15-20MZ22-01_0-20_COMPMZ22-01_55-60MZ22-01_65-70MZ22-01_55-70_COMP
7MZ22-01_20-25MZ22-01_20-40_COMPMZ22-01_70-75MZ22-01_80-85MZ22-01_70-85_COMP
8MZ22-01_25-30DO NOT USE IN COMPOSITEMZ22-01_85-90MZ22-01_95-100MZ22-01_85-100_COMP
9MZ22-01_30-35DO NOT USE IN COMPOSITEMZ22-01_100-105MZ22-01_115-120MZ22-01_100-120_COMP
10MZ22-01_35-40MZ22-01_20-40_COMPMZ22-01_120-125MZ22-01_135-140MZ22-01_120-140_COMP
11MZ22-01_40-45MZ22-01_40-55_COMPMZ22-01_140-145MZ22-01_155-160MZ22-01_140-160_COMP
12MZ22-01_45-50DO NOT USE IN COMPOSITEMZ22-01_160-165MZ22-01_175-180MZ22-01_160-180_COMP
13MZ22-01_45-50ADO NOT USE IN COMPOSITEMZ22-01_180-185MZ22-01_195-200MZ22-01_180-200_COMP
14MZ22-01_50-55MZ22-01_40-55_COMPMZ22-01_200-205MZ22-01_215-220MZ22-01_200-220_COMP
15MZ22-01_55-60MZ22-01_55-70_COMPMZ22-01_220-225MZ22-01_235-240MZ22-01_220-240_COMP
16MZ22-01_60-65DO NOT USE IN COMPOSITEMZ22-01_240-245MZ22-01_255-260MZ22-01_240-260_COMP
17MZ22-01_65-70MZ22-01_55-70_COMPMZ22-01_260-265MZ22-01_275-280MZ22-01_260-280_COMP
18MZ22-01_70-75MZ22-01_70-85_COMPMZ22-01_280-285MZ22-01_295-300MZ22-01_280-300_COMP
19MZ22-01_75-80DO NOT USE IN COMPOSITEMZ22-01_300-305MZ22-01_315-320MZ22-01_300-320_COMP
20MZ22-01_80-85MZ22-01_70-85_COMPMZ22-01_320-325MZ22-01_335-340MZ22-01_320-340_COMP
21MZ22-01_85-90MZ22-01_85-100_COMPMZ22-01_340-345MZ22-01_355-360MZ22-01_340-360_COMP
22MZ22-01_90-95DO NOT USE IN COMPOSITEMZ22-01_360-365MZ22-01_375-380MZ22-01_360-380_COMP
23MZ22-01_95-100MZ22-01_85-100_COMPMZ22-01_380-385MZ22-01_395-400MZ22-01_380-400_COMP
24MZ22-01_95-100CDO NOT USE IN COMPOSITEMZ22-01_400-405MZ22-01_415-420MZ22-01_400-420_COMP
25MZ22-01_100-105MZ22-01_100-120_COMPMZ22-01_420-425MZ22-01_435-440MZ22-01_420-440_COMP
26MZ22-01_105-110DO NOT USE IN COMPOSITEMZ22-01_440-445MZ22-01_455-460MZ22-01_440-460_COMP
27MZ22-01_110-115DO NOT USE IN COMPOSITEMZ22-01_460-465MZ22-01_475-480MZ22-01_460-480_COMP
28MZ22-01_115-120MZ22-01_100-120_COMPMZ22-01_480-485MZ22-01_495-500MZ22-01_480-500_COMP
29MZ22-01_120-125MZ22-01_120-140_COMPMZ22-01_500-505MZ22-01_515-520MZ22-01_500-520_COMP
30MZ22-01_125-130DO NOT USE IN COMPOSITEMZ22-01_520-525MZ22-01_535-540MZ22-01_520-540_COMP
31MZ22-01_130-135DO NOT USE IN COMPOSITEMZ22-01_540-545MZ22-01_555-560MZ22-01_540-560_COMP
32MZ22-01_135-140MZ22-01_120-140_COMP
33MZ22-01_140-145MZ22-01_140-160_COMP
34MZ22-01_145-150DO NOT USE IN COMPOSITE
35MZ22-01_140-145BDO NOT USE IN COMPOSITE
36MZ22-01_150-155DO NOT USE IN COMPOSITE
37MZ22-01_155-160MZ22-01_140-160_COMP
38MZ22-01_160-165MZ22-01_160-180_COMP
39MZ22-01_165-170DO NOT USE IN COMPOSITE
40MZ22-01_170-175DO NOT USE IN COMPOSITE
41MZ22-01_175-180MZ22-01_160-180_COMP
42MZ22-01_180-185MZ22-01_180-200_COMP
43MZ22-01_185-190DO NOT USE IN COMPOSITE
44MZ22-01_190-195DO NOT USE IN COMPOSITE
45MZ22-01_195-200MZ22-01_180-200_COMP
46MZ22-01_195-200ADO NOT USE IN COMPOSITE
47MZ22-01_200-205MZ22-01_200-220_COMP
48MZ22-01_205-210DO NOT USE IN COMPOSITE
49MZ22-01_210-215DO NOT USE IN COMPOSITE
50MZ22-01_215-220MZ22-01_200-220_COMP
51MZ22-01_220-225MZ22-01_220-240_COMP
52MZ22-01_225-230DO NOT USE IN COMPOSITE
53MZ22-01_230-235DO NOT USE IN COMPOSITE
54MZ22-01_235-240MZ22-01_220-240_COMP
Sheet1
Cell Formulas
RangeFormula
C2:C54C2=XLOOKUP(A2,VSTACK($F$3:$F$31,$G$3:$G$31),VSTACK($H$3:$H$31,$H$3:$H$31),"DO NOT USE IN COMPOSITE")
 
Upvote 0
Great! Almost! I also need the sample numbers that fall between the range to be assigned the new CompID number. So A4 (MZ22-01_5-10) should also have the compID of MZ22-01_0-20_COMP since it falls within the range of compStart and compEND.
So A3 through A6 should have the new compID of MZ22-01_0-20_COMP since they all fall within the range of CompStart and CompEnd.
 
Upvote 0
Are the prefixes always going to be in the format of "MZ##-##_" or whatever letter combination? And will the ranges always be "###-###"?

If so, give this a try. If not, please give samples of all the possible formats for the IDs.

Book1 11-27-2023.xlsx
ABCDEFGH
1Sample_IDNew_Composite_Sample_IDCompSTARTCompENDCompID
2MZ22-01_0BDO NOT USE IN COMPOSITE
3MZ22-01_0-5MZ22-01_0-20_COMPMZ22-01_0-5MZ22-01_15-20MZ22-01_0-20_COMP
4MZ22-01_5-10MZ22-01_0-20_COMPMZ22-01_20-25MZ22-01_35-40MZ22-01_20-40_COMP
5MZ22-01_10-15MZ22-01_0-20_COMPMZ22-01_40-45MZ22-01_50-55MZ22-01_40-55_COMP
6MZ22-01_15-20MZ22-01_0-20_COMPMZ22-01_55-60MZ22-01_65-70MZ22-01_55-70_COMP
7MZ22-01_20-25MZ22-01_20-40_COMPMZ22-01_70-75MZ22-01_80-85MZ22-01_70-85_COMP
8MZ22-01_25-30MZ22-01_20-40_COMPMZ22-01_85-90MZ22-01_95-100MZ22-01_85-100_COMP
9MZ22-01_30-35MZ22-01_20-40_COMPMZ22-01_100-105MZ22-01_115-120MZ22-01_100-120_COMP
10MZ22-01_35-40MZ22-01_20-40_COMPMZ22-01_120-125MZ22-01_135-140MZ22-01_120-140_COMP
11MZ22-01_40-45MZ22-01_40-55_COMPMZ22-01_140-145MZ22-01_155-160MZ22-01_140-160_COMP
12MZ22-01_45-50MZ22-01_40-55_COMPMZ22-01_160-165MZ22-01_175-180MZ22-01_160-180_COMP
13MZ22-01_45-50ADO NOT USE IN COMPOSITEMZ22-01_180-185MZ22-01_195-200MZ22-01_180-200_COMP
14MZ22-01_50-55MZ22-01_40-55_COMPMZ22-01_200-205MZ22-01_215-220MZ22-01_200-220_COMP
15MZ22-01_55-60MZ22-01_55-70_COMPMZ22-01_220-225MZ22-01_235-240MZ22-01_220-240_COMP
16MZ22-01_60-65MZ22-01_55-70_COMPMZ22-01_240-245MZ22-01_255-260MZ22-01_240-260_COMP
17MZ22-01_65-70MZ22-01_55-70_COMPMZ22-01_260-265MZ22-01_275-280MZ22-01_260-280_COMP
18MZ22-01_70-75MZ22-01_70-85_COMPMZ22-01_280-285MZ22-01_295-300MZ22-01_280-300_COMP
19MZ22-01_75-80MZ22-01_70-85_COMPMZ22-01_300-305MZ22-01_315-320MZ22-01_300-320_COMP
20MZ22-01_80-85MZ22-01_70-85_COMPMZ22-01_320-325MZ22-01_335-340MZ22-01_320-340_COMP
21MZ22-01_85-90MZ22-01_85-100_COMPMZ22-01_340-345MZ22-01_355-360MZ22-01_340-360_COMP
22MZ22-01_90-95MZ22-01_85-100_COMPMZ22-01_360-365MZ22-01_375-380MZ22-01_360-380_COMP
23MZ22-01_95-100MZ22-01_85-100_COMPMZ22-01_380-385MZ22-01_395-400MZ22-01_380-400_COMP
24MZ22-01_95-100CDO NOT USE IN COMPOSITEMZ22-01_400-405MZ22-01_415-420MZ22-01_400-420_COMP
25MZ22-01_100-105MZ22-01_100-120_COMPMZ22-01_420-425MZ22-01_435-440MZ22-01_420-440_COMP
26MZ22-01_105-110MZ22-01_100-120_COMPMZ22-01_440-445MZ22-01_455-460MZ22-01_440-460_COMP
27MZ22-01_110-115MZ22-01_100-120_COMPMZ22-01_460-465MZ22-01_475-480MZ22-01_460-480_COMP
28MZ22-01_115-120MZ22-01_100-120_COMPMZ22-01_480-485MZ22-01_495-500MZ22-01_480-500_COMP
29MZ22-01_120-125MZ22-01_120-140_COMPMZ22-01_500-505MZ22-01_515-520MZ22-01_500-520_COMP
30MZ22-01_125-130MZ22-01_120-140_COMPMZ22-01_520-525MZ22-01_535-540MZ22-01_520-540_COMP
31MZ22-01_130-135MZ22-01_120-140_COMPMZ22-01_540-545MZ22-01_555-560MZ22-01_540-560_COMP
32MZ22-01_135-140MZ22-01_120-140_COMP
33MZ22-01_140-145MZ22-01_140-160_COMP
34MZ22-01_145-150MZ22-01_140-160_COMP
35MZ22-01_140-145BDO NOT USE IN COMPOSITE
36MZ22-01_150-155MZ22-01_140-160_COMP
37MZ22-01_155-160MZ22-01_140-160_COMP
38MZ22-01_160-165MZ22-01_160-180_COMP
39MZ22-01_165-170MZ22-01_160-180_COMP
40MZ22-01_170-175MZ22-01_160-180_COMP
41MZ22-01_175-180MZ22-01_160-180_COMP
42MZ22-01_180-185MZ22-01_180-200_COMP
43MZ22-01_185-190MZ22-01_180-200_COMP
44MZ22-01_190-195MZ22-01_180-200_COMP
45MZ22-01_195-200MZ22-01_180-200_COMP
46MZ22-01_195-200ADO NOT USE IN COMPOSITE
47MZ22-01_200-205MZ22-01_200-220_COMP
48MZ22-01_205-210MZ22-01_200-220_COMP
49MZ22-01_210-215MZ22-01_200-220_COMP
50MZ22-01_215-220MZ22-01_200-220_COMP
51MZ22-01_220-225MZ22-01_220-240_COMP
52MZ22-01_225-230MZ22-01_220-240_COMP
53MZ22-01_230-235MZ22-01_220-240_COMP
54MZ22-01_235-240MZ22-01_220-240_COMP
Sheet1
Cell Formulas
RangeFormula
C2:C54C2=IFERROR(XLOOKUP(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("_",A2)),"-",".")*1,SUBSTITUTE(RIGHT($F$3:$F$31,LEN($F$3:$F$31)-FIND("_",$F$3:$F$31)),"-",".")*1,$H$3:$H$31,"DO NOT USE IN COMPOSITE",-1),"DO NOT USE IN COMPOSITE")
 
Upvote 0
Solution
Are the prefixes always going to be in the format of "MZ##-##_" or whatever letter combination? And will the ranges always be "###-###"?

If so, give this a try. If not, please give samples of all the possible formats for the IDs.

Book1 11-27-2023.xlsx
ABCDEFGH
1Sample_IDNew_Composite_Sample_IDCompSTARTCompENDCompID
2MZ22-01_0BDO NOT USE IN COMPOSITE
3MZ22-01_0-5MZ22-01_0-20_COMPMZ22-01_0-5MZ22-01_15-20MZ22-01_0-20_COMP
4MZ22-01_5-10MZ22-01_0-20_COMPMZ22-01_20-25MZ22-01_35-40MZ22-01_20-40_COMP
5MZ22-01_10-15MZ22-01_0-20_COMPMZ22-01_40-45MZ22-01_50-55MZ22-01_40-55_COMP
6MZ22-01_15-20MZ22-01_0-20_COMPMZ22-01_55-60MZ22-01_65-70MZ22-01_55-70_COMP
7MZ22-01_20-25MZ22-01_20-40_COMPMZ22-01_70-75MZ22-01_80-85MZ22-01_70-85_COMP
8MZ22-01_25-30MZ22-01_20-40_COMPMZ22-01_85-90MZ22-01_95-100MZ22-01_85-100_COMP
9MZ22-01_30-35MZ22-01_20-40_COMPMZ22-01_100-105MZ22-01_115-120MZ22-01_100-120_COMP
10MZ22-01_35-40MZ22-01_20-40_COMPMZ22-01_120-125MZ22-01_135-140MZ22-01_120-140_COMP
11MZ22-01_40-45MZ22-01_40-55_COMPMZ22-01_140-145MZ22-01_155-160MZ22-01_140-160_COMP
12MZ22-01_45-50MZ22-01_40-55_COMPMZ22-01_160-165MZ22-01_175-180MZ22-01_160-180_COMP
13MZ22-01_45-50ADO NOT USE IN COMPOSITEMZ22-01_180-185MZ22-01_195-200MZ22-01_180-200_COMP
14MZ22-01_50-55MZ22-01_40-55_COMPMZ22-01_200-205MZ22-01_215-220MZ22-01_200-220_COMP
15MZ22-01_55-60MZ22-01_55-70_COMPMZ22-01_220-225MZ22-01_235-240MZ22-01_220-240_COMP
16MZ22-01_60-65MZ22-01_55-70_COMPMZ22-01_240-245MZ22-01_255-260MZ22-01_240-260_COMP
17MZ22-01_65-70MZ22-01_55-70_COMPMZ22-01_260-265MZ22-01_275-280MZ22-01_260-280_COMP
18MZ22-01_70-75MZ22-01_70-85_COMPMZ22-01_280-285MZ22-01_295-300MZ22-01_280-300_COMP
19MZ22-01_75-80MZ22-01_70-85_COMPMZ22-01_300-305MZ22-01_315-320MZ22-01_300-320_COMP
20MZ22-01_80-85MZ22-01_70-85_COMPMZ22-01_320-325MZ22-01_335-340MZ22-01_320-340_COMP
21MZ22-01_85-90MZ22-01_85-100_COMPMZ22-01_340-345MZ22-01_355-360MZ22-01_340-360_COMP
22MZ22-01_90-95MZ22-01_85-100_COMPMZ22-01_360-365MZ22-01_375-380MZ22-01_360-380_COMP
23MZ22-01_95-100MZ22-01_85-100_COMPMZ22-01_380-385MZ22-01_395-400MZ22-01_380-400_COMP
24MZ22-01_95-100CDO NOT USE IN COMPOSITEMZ22-01_400-405MZ22-01_415-420MZ22-01_400-420_COMP
25MZ22-01_100-105MZ22-01_100-120_COMPMZ22-01_420-425MZ22-01_435-440MZ22-01_420-440_COMP
26MZ22-01_105-110MZ22-01_100-120_COMPMZ22-01_440-445MZ22-01_455-460MZ22-01_440-460_COMP
27MZ22-01_110-115MZ22-01_100-120_COMPMZ22-01_460-465MZ22-01_475-480MZ22-01_460-480_COMP
28MZ22-01_115-120MZ22-01_100-120_COMPMZ22-01_480-485MZ22-01_495-500MZ22-01_480-500_COMP
29MZ22-01_120-125MZ22-01_120-140_COMPMZ22-01_500-505MZ22-01_515-520MZ22-01_500-520_COMP
30MZ22-01_125-130MZ22-01_120-140_COMPMZ22-01_520-525MZ22-01_535-540MZ22-01_520-540_COMP
31MZ22-01_130-135MZ22-01_120-140_COMPMZ22-01_540-545MZ22-01_555-560MZ22-01_540-560_COMP
32MZ22-01_135-140MZ22-01_120-140_COMP
33MZ22-01_140-145MZ22-01_140-160_COMP
34MZ22-01_145-150MZ22-01_140-160_COMP
35MZ22-01_140-145BDO NOT USE IN COMPOSITE
36MZ22-01_150-155MZ22-01_140-160_COMP
37MZ22-01_155-160MZ22-01_140-160_COMP
38MZ22-01_160-165MZ22-01_160-180_COMP
39MZ22-01_165-170MZ22-01_160-180_COMP
40MZ22-01_170-175MZ22-01_160-180_COMP
41MZ22-01_175-180MZ22-01_160-180_COMP
42MZ22-01_180-185MZ22-01_180-200_COMP
43MZ22-01_185-190MZ22-01_180-200_COMP
44MZ22-01_190-195MZ22-01_180-200_COMP
45MZ22-01_195-200MZ22-01_180-200_COMP
46MZ22-01_195-200ADO NOT USE IN COMPOSITE
47MZ22-01_200-205MZ22-01_200-220_COMP
48MZ22-01_205-210MZ22-01_200-220_COMP
49MZ22-01_210-215MZ22-01_200-220_COMP
50MZ22-01_215-220MZ22-01_200-220_COMP
51MZ22-01_220-225MZ22-01_220-240_COMP
52MZ22-01_225-230MZ22-01_220-240_COMP
53MZ22-01_230-235MZ22-01_220-240_COMP
54MZ22-01_235-240MZ22-01_220-240_COMP
Sheet1
Cell Formulas
RangeFormula
C2:C54C2=IFERROR(XLOOKUP(SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("_",A2)),"-",".")*1,SUBSTITUTE(RIGHT($F$3:$F$31,LEN($F$3:$F$31)-FIND("_",$F$3:$F$31)),"-",".")*1,$H$3:$H$31,"DO NOT USE IN COMPOSITE",-1),"DO NOT USE IN COMPOSITE")

THANKS! This looks like it is working. I think the format will/should be the same. Your help is much appreciated!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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