Formula help--Vlookup?

[BeckyB]

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?

 

[Eduardo]

Hi,
Could you post an example of your data and what you want to achieve, thanks

 

[BeckyB]

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#)

 

[Roger Govier]

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

 

[BeckyB]

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]

Hi Becky

OK.
Then on sheet1, in cell B2 enter
=INDEX(Sheet2!$C:$C,MATCH("*"&B2,Sheet2!$B:$B,0))
and then copy down

 

[BeckyB]

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]

Hi Becky

Format the column with the formula in the style of your choice.

 

[BeckyB]

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