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

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?

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

On Sep 18, 3:18*pm, winnie123
wrote:
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?

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" wrote:

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?

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

On Sep 18, 4:58*pm, winnie123
wrote:
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" wrote:
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 -

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

On Sep 18, 5:12*pm, Pete_UK wrote:
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

On Sep 18, 4:58*pm, winnie123
wrote:



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" wrote:
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

Thanks Pete,

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

Winnie

"Pete_UK" wrote:

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

On Sep 18, 5:12 pm, Pete_UK wrote:
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

On Sep 18, 4:58 pm, winnie123
wrote:



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" wrote:
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

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

Pete

On Sep 18, 8:15*pm, winnie123
wrote:
Thanks Pete,

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

Winnie