lookup a category for a part that contains a start and end value

[winnie123]

Can anyone help? I have a worksheet containing part numbers and I want to
search another workbook which contains a product category. The trouble is the
product category only shows the first part number and last part number, its
not a full list. How can I use look up to return the product category?

 

[Pete_UK]

You can use wildcards with VLOOKUP. Give a few details of how your
data is laid out and what you want to do so that we can advise you
more fully.

Pete

 

[winnie123]

Hi,

I have the following headers for my sales report which looks at sales of
spare parts. The column headers are from A1 to N1, I have shown a small
example, not all columns. I need to enter the fomula in column M.

I was using

=VLOOKUP(+E2,'[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$C$69,1,TRUE)
Which worked for the first row but then the other rows just seemed to
select any product category.



Customer Order No Invoice No Class Part Number
1 180077 324752 40 GP2SPSXX00021
10082 170926 310216 40 SCNDORXX00067
10116 164529 300791 40 SYCOMNXX00074

I need to look up Part number in another workbook which cantains a start
range and end range for eaxample

CATEGORY START END
ARDAC T0000000001 T9999999999
ARDAC ELITE ARDACECB00001 ARDACECB99999
ARDAC ELITE B5EC01XX00001 BE5S03XX99999
ARDAC ELITE SARDELXX00001 SARDELXX99999
ARDAC5 BA5C01AA00001 BA5S01ZZ99999
CONDOR SCNDORXX00001 SCNDORXX99999
CONDOR SCPLUSXX00001 SCPLUSXX99999
CONDOR SCPREMXX00001 SCPREMXX99999

Hope I have given you enough info. Thanks

 

[Pete_UK]

I presume you want the category to appear to the right of the Part
Number. In that case try this in F2:

=INDEX('[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$A$69,MATCH(E2,'[PARTS
CATEGORY 1.xls]Sheet1'!$B$2:$B$69))

For this to work the Parts Category file will need to be sorted on the
Start field - is that acceptable?

Hope this helps.

Pete

Hi,

I have the following headers for my sales report which looks at sales of
spare parts. The column headers are from A1 to N1, I have shown a small
example, not all columns. I need to enter the fomula in column M.

I was using

=VLOOKUP(+E2,'[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$C$69,1,TRUE)
 Which worked for the first row but then the other rows just seemed to
select any product category.

Customer        Order No        Invoice No      Class   Part Number
1       180077  324752  40      GP2SPSXX00021
10082   170926  310216  40      SCNDORXX00067
10116   164529  300791  40      SYCOMNXX00074

I need to look up Part number in another workbook which cantains a start
range and end range  for eaxample

CATEGORY                                   START        END
ARDAC                            T0000000001    T9999999999
ARDAC ELITE          ARDACECB00001      ARDACECB99999
ARDAC ELITE          B5EC01XX00001      BE5S03XX99999
ARDAC ELITE         SARDELXX00001       SARDELXX99999
ARDAC5                      BA5C01AA00001       BA5S01ZZ99999
CONDOR                    SCNDORXX00001 SCNDORXX99999
CONDOR                   SCPLUSXX00001  SCPLUSXX99999
CONDOR                   SCPREMXX00001  SCPREMXX99999

Hope I have given you enough info. Thanks      

Can anyone help? I have a worksheet containing part numbers and I want to
search another workbook which contains a product category. The trouble is the
product category only shows the first part number and last part number,its
not a full list. How can I use look up to return the product category?-Hide quoted text -

- Show quoted text -

 

[Pete_UK]

I just realised that you said your example does not show all columns -
I've assumed your Part Number is in E2, so change this to suit and put
the formula in M2.

Hope this helps.

Pete

I presume you want the category to appear to the right of the Part
Number. In that case try this in F2:

=INDEX('[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$A$69,MATCH(E2,'[PARTS
CATEGORY 1.xls]Sheet1'!$B$2:$B$69))

For this to work the Parts Category file will need to be sorted on the
Start field - is that acceptable?

Hope this helps.

Pete

Hi,

I have the following headers for my sales report which looks at sales of
spare parts. The column headers are from A1 to N1, I have shown a small
example, not all columns. I need to enter the fomula in column M.

I was using

=VLOOKUP(+E2,'[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$C$69,1,TRUE)
 Which worked for the first row but then the other rows just seemed to
select any product category.

Customer        Order No        Invoice No      Class   Part Number
1       180077  324752  40      GP2SPSXX00021
10082   170926  310216  40      SCNDORXX00067
10116   164529  300791  40      SYCOMNXX00074

I need to look up Part number in another workbook which cantains a start
range and end range  for eaxample

CATEGORY                                   START        END
ARDAC                            T0000000001    T9999999999
ARDAC ELITE          ARDACECB00001      ARDACECB99999
ARDAC ELITE          B5EC01XX00001      BE5S03XX99999
ARDAC ELITE         SARDELXX00001       SARDELXX99999
ARDAC5                      BA5C01AA00001      BA5S01ZZ99999
CONDOR                    SCNDORXX00001 SCNDORXX99999
CONDOR                   SCPLUSXX00001  SCPLUSXX99999
CONDOR                   SCPREMXX00001  SCPREMXX99999

Hope I have given you enough info. Thanks      

 

[winnie123]

Thanks Pete,

That worked a treat, I have just checked all 14,000 records and everyone is
correct.

Winnie

I just realised that you said your example does not show all columns -
I've assumed your Part Number is in E2, so change this to suit and put
the formula in M2.

Hope this helps.

Pete

I presume you want the category to appear to the right of the Part
Number. In that case try this in F2:

=INDEX('[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$A$69,MATCH(E2,'[PARTS
CATEGORY 1.xls]Sheet1'!$B$2:$B$69))

For this to work the Parts Category file will need to be sorted on the
Start field - is that acceptable?

Hope this helps.

Pete

Hi,

I have the following headers for my sales report which looks at sales of
spare parts. The column headers are from A1 to N1, I have shown a small
example, not all columns. I need to enter the fomula in column M.

I was using

=VLOOKUP(+E2,'[PARTS CATEGORY 1.xls]Sheet1'!$A$2:$C$69,1,TRUE)
Which worked for the first row but then the other rows just seemed to
select any product category.

Customer Order No Invoice No Class Part Number
1 180077 324752 40 GP2SPSXX00021
10082 170926 310216 40 SCNDORXX00067
10116 164529 300791 40 SYCOMNXX00074

I need to look up Part number in another workbook which cantains a start
range and end range for eaxample

CATEGORY START END
ARDAC T0000000001 T9999999999
ARDAC ELITE ARDACECB00001 ARDACECB99999
ARDAC ELITE B5EC01XX00001 BE5S03XX99999
ARDAC ELITE SARDELXX00001 SARDELXX99999
ARDAC5 BA5C01AA00001 BA5S01ZZ99999
CONDOR SCNDORXX00001 SCNDORXX99999
CONDOR SCPLUSXX00001 SCPLUSXX99999
CONDOR SCPREMXX00001 SCPREMXX99999

Hope I have given you enough info. Thanks

:
Can anyone help? I have a worksheet containing part numbers and I want to
search another workbook which contains a product category. The trouble is the
product category only shows the first part number and last part number, its
not a full list. How can I use look up to return the product category

 

[Pete_UK]

Glad to hear that, Winnie - thanks for feeding back.

Pete