| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help--Vlookup?
I need help wrinting a formula for the below.
Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
Hi,
Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
This is the data to search for in the formulas (each is a different column):
Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
Hi Becky
I put the values nb, 3m etc in G1:G4, then used this formula in H1 =INDEX($C:$C,MATCH("*"&G1,$B:$B,0)) and copy down -- Regards Roger Govier BeckyB wrote: This is the data to search for in the formulas (each is a different column): Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
I guess I am not fully understanding where to put your formula to pull
correctly. This is on 1st worksheet: 115-680G nb formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) This information is on the 2nd worksheet, each header a separate column: Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 "Roger Govier" wrote: Hi Becky I put the values nb, 3m etc in G1:G4, then used this formula in H1 =INDEX($C:$C,MATCH("*"&G1,$B:$B,0)) and copy down -- Regards Roger Govier BeckyB wrote: This is the data to search for in the formulas (each is a different column): Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
Hi Becky
OK. Then on sheet1, in cell B2 enter =INDEX(Sheet2!$C:$C,MATCH("*"&B2,Sheet2!$B:$B,0)) and then copy down -- Regards Roger Govier BeckyB wrote: I guess I am not fully understanding where to put your formula to pull correctly. This is on 1st worksheet: 115-680G nb formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) This information is on the 2nd worksheet, each header a separate column: Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 "Roger Govier" wrote: Hi Becky I put the values nb, 3m etc in G1:G4, then used this formula in H1 =INDEX($C:$C,MATCH("*"&G1,$B:$B,0)) and copy down -- Regards Roger Govier BeckyB wrote: This is the data to search for in the formulas (each is a different column): Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
Thanks! I got it to work, but one other question on this. How can I include
in the formula the Style Number in A1? I have multiple styles within this report I need to do this to. "Roger Govier" wrote: Hi Becky OK. Then on sheet1, in cell B2 enter =INDEX(Sheet2!$C:$C,MATCH("*"&B2,Sheet2!$B:$B,0)) and then copy down -- Regards Roger Govier BeckyB wrote: I guess I am not fully understanding where to put your formula to pull correctly. This is on 1st worksheet: 115-680G nb formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) This information is on the 2nd worksheet, each header a separate column: Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 "Roger Govier" wrote: Hi Becky I put the values nb, 3m etc in G1:G4, then used this formula in H1 =INDEX($C:$C,MATCH("*"&G1,$B:$B,0)) and copy down -- Regards Roger Govier BeckyB wrote: This is the data to search for in the formulas (each is a different column): Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
Hi Becky
Format the column with the formula in the style of your choice. -- Regards Roger Govier BeckyB wrote: Thanks! I got it to work, but one other question on this. How can I include in the formula the Style Number in A1? I have multiple styles within this report I need to do this to. "Roger Govier" wrote: Hi Becky OK. Then on sheet1, in cell B2 enter =INDEX(Sheet2!$C:$C,MATCH("*"&B2,Sheet2!$B:$B,0)) and then copy down -- Regards Roger Govier BeckyB wrote: I guess I am not fully understanding where to put your formula to pull correctly. This is on 1st worksheet: 115-680G nb formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) This information is on the 2nd worksheet, each header a separate column: Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 "Roger Govier" wrote: Hi Becky I put the values nb, 3m etc in G1:G4, then used this formula in H1 =INDEX($C:$C,MATCH("*"&G1,$B:$B,0)) and copy down -- Regards Roger Govier BeckyB wrote: This is the data to search for in the formulas (each is a different column): Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? . . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula help--Vlookup?
I don't understand. I am not looking at formating the cell. I need to lookup
various styles & sizes and get the item number. The example you gave below worked for 1st style number, but didn't work for any others. Can I write the formula to look at A1 & A2 to give me the item number for all? =INDEX(Sheet2!$C:$C,MATCH("*"&B2,Sheet2!$B:$B,0)) This is on 1st worksheet: 115-680G 115-681P nb formula (need Item# from 2nd worksheet) nb formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) This information is on the 2nd worksheet, each header a separate column: Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 115-681P TRY FOOTSI SNP PINK NB 02339664-1 115-681P TRY FOOTSI SNP PINK 3M 02339665-8 115-681P TRY FOOTSI SNP PINK 6M 02339666-6 115-681P TRY FOOTSI SNP PINK 9M 02339667-4 115-682W TRY FOOTSI SNP WHITE NB 02339668-2 115-682W TRY FOOTSI SNP WHITE 3M 02339669-0 115-682W TRY FOOTSI SNP WHITE 6M 02339670-8 115-682W TRY FOOTSI SNP WHITE 9M 02339671-6 115-683LC TRY FOOTSI SNP LILAC NB 02339672-4 115-683LC TRY FOOTSI SNP LILAC 3M 02339673-2 115-683LC TRY FOOTSI SNP LILAC 6M 02339674-0 115-683LC TRY FOOTSI SNP LILAC 9M 02339675-7 "Roger Govier" wrote: Hi Becky Format the column with the formula in the style of your choice. -- Regards Roger Govier BeckyB wrote: Thanks! I got it to work, but one other question on this. How can I include in the formula the Style Number in A1? I have multiple styles within this report I need to do this to. "Roger Govier" wrote: Hi Becky OK. Then on sheet1, in cell B2 enter =INDEX(Sheet2!$C:$C,MATCH("*"&B2,Sheet2!$B:$B,0)) and then copy down -- Regards Roger Govier BeckyB wrote: I guess I am not fully understanding where to put your formula to pull correctly. This is on 1st worksheet: 115-680G nb formula (need Item# from 2nd worksheet) 3m formula (need Item# from 2nd worksheet) 6m formula (need Item# from 2nd worksheet) 9m formula (need Item# from 2nd worksheet) This information is on the 2nd worksheet, each header a separate column: Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 "Roger Govier" wrote: Hi Becky I put the values nb, 3m etc in G1:G4, then used this formula in H1 =INDEX($C:$C,MATCH("*"&G1,$B:$B,0)) and copy down -- Regards Roger Govier BeckyB wrote: This is the data to search for in the formulas (each is a different column): Style# Item Description Item # 115-680G TRY FOOTSI SNP GREEN NB 02339660-9 115-680G TRY FOOTSI SNP GREEN 3M 02339661-7 115-680G TRY FOOTSI SNP GREEN 6M 02339662-5 115-680G TRY FOOTSI SNP GREEN 9M 02339663-3 This is where I need it to go (this is where I put the formulas): 115-680G nb (Item#) 3m (Item#) 6m (Item#) 9m (Item#) "Eduardo" wrote: Hi, Could you post an example of your data and what you want to achieve, thanks "BeckyB" wrote: I need help wrinting a formula for the below. Sheet 1 A1=Style Number B1=Size (text value) C1=part number (formula result) Sheet2 contains the data. There are multiple entries of the style number (A1) on sheet 2. The size is actually part of the TEXT description of that style. I need the formula to give me the part number of that Size in that Style number. Is there a way to write the formula to look for the Style number, then look in that description for the text value and when they match, give me the part number 1 column over? . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I combine a VLOOKUP formula and a NETWORKDAYS formula? | Excel Worksheet Functions | |||
| Alternative formula to the vlookup formula? | Excel Worksheet Functions | |||
| convert vlookup formula to link formula | Excel Worksheet Functions | |||
| VLOOKUP best formula to use? | Excel Discussion (Misc queries) | |||
| Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
Linear Mode
