Formula HELLLPPP!

[BeckyB]

I did a foo-pa. I accidently deleted a previous thread of mine, so I am
hoping someone there can help me finish it. I have Sheet1 where I need the
formulas and Report sheet is where the data is.

I have this formula
=LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$F,MATCH("*"&B20,'Report'!$F:$F,0))))

And the results are this:
115-680G
nb 02339660-9
3m 02339661-7
6m 02339662-5
9m 02339663-3

However, I need to have this occur for multiple Style numbers (ie 115-680G).
This formula works for the above, but nowhere else. I want it to look at the
Style number and then search for the text (NB) and then grab the Item Number.

Can someone tell me how to get this main formula to work for all?
115-680G 115-681P 115-682W 115-683LC
nb 02339660-9 nb nb nb
3m 02339661-7 3m 3m 3m
6m 02339662-5 6m 6m 6m
9m 02339663-3 9m 9m 9m

This is my data:
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
115-684A TRY FOOTSI SNP AZURE NB 02339676-5
115-684A TRY FOOTSI SNP AZURE 3M 02339677-3
115-684A TRY FOOTSI SNP AZURE 6M 02339678-1
115-684A TRY FOOTSI SNP AZURE 9M 02339680-7


Roger was helping me, but since I accidently deleted the post, I cannot get
him to help further.

Thanks,
BeckyB

 

[T. Valko]

It's not clear what you're wanting to do.

Do you want to lookup a style: 115-680G, and then return all the part
numbers for that style:

02339660-9
02339661-7
02339662-5
02339663-3

I have this formula:
=LOOKUP($B$18,'Report'!$A:$A,(INDEX('Report'!$F:$F,MATCH("*"&B20,'Report'!$F:$F,0))))

So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's
in B20?

Is your data sorted or grouped together as is shown in the sample data you
posted? Does every style have 4 part numbers as is shown in the sample data?

 

[BeckyB]

Thanks for trying to help!

Yes, every style has 4 item numbers associated with it. I want it to lookup
the Style number, then refer to the item size (NB) in the text description to
pull the part number. I am not sure what the different parts of the formula
are since it was provided to me by someone else.

$B$18 is where I have the Style number 115-680G on Sheet1. B20 is where the
1st size is displayed (NB) for that style. These locations will change for
each style.

(B18) 115-680G
(B19) empty cell
(B20) nb 02339660-9
(B21) 3m 02339661-7
(B22) 6m 02339662-5
(B23) 9m 02339663-3


Do you want to lookup a style: 115-680G, and then return all the part
numbers for that style: YES

So what's in $B$18 and why are you using a wildcard for "*"&B20 and what's
in B20? DON'T KNOW WHAT WILDCARD IS, SOMEONE ELSE WROTE FORMULA. B18 IS THE
STYLE NUMBER & B20 IS THE SIZE TO LOOKUP THE PART NUMBER FOR.

Is your data sorted or grouped together as is shown in the sample data you
posted? DATA IS THOSE SIZES, BUT DIFFERENT STYLE NUMBERS. EACH DISPLAYED AS
THIS ON SHEET1:
115-681P
nb
3m
6m
9m

115-682W
nb
3m
6m
9m

DISPLAYED AS THIS IN REPORT TAB:
Style# Item Description Part#
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
115-684A TRY FOOTSI SNP AZURE NB 02339676-5
115-684A TRY FOOTSI SNP AZURE 3M 02339677-3
115-684A TRY FOOTSI SNP AZURE 6M 02339678-1
115-684A TRY FOOTSI SNP AZURE 9M 02339680-7

Does every style have 4 part numbers as is shown in the sample data? YES

 

[T. Valko]

Ok, not sure how your data is setup but see if this sample file helps.

zBeckyB.xls 15kb

http://cjoint.com/?efve3nu2CJ

 

[BeckyB]

YES, but not quite.

Can you explaing the +ROWS(F$2:F2)-1) part of the formula?

I need it to only bring back the Part number for a specific size (3M, NB)
found in the text. There might be an occasion when 1 Style may have another
size the others don't and the data will not always be in order.

Size
NB
3M
6M
9M
12M
18M
24M