|
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? |
lookup a category for a part that contains a start and end value
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? |
lookup a category for a part that contains a start and end value
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? |
lookup a category for a part that contains a start and end value
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 - |
lookup a category for a part that contains a start and end value
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 |
lookup a category for a part that contains a start and end val
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 |
lookup a category for a part that contains a start and end val
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 |
| All times are GMT +1. The time now is 09:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com