# Find UPC code from material numbers and populate 3 bracket prices

1. ## Find UPC code from material numbers and populate 3 bracket prices

On Sheet 1 I have a long pricing list where there are material numbers and bracket prices for each material number.

On Sheet 2 I would like to enter 5 digit UPC codes and have the bracket prices auto populate for that UPC code.

Example:
Sheet 1

Long Material Number
12345678904

Old Material
12345678901

Material Number
123456789

Sheet 2
UPC
56789

I tried using VLOOKUP but that only works if upc and material number are both 5 digits.

Example.xlsxCan anyone help me out?

2. ## Re: Find UPC code from material numbers and populate 3 bracket prices

With first number in sheet2!A3:
Type in sheet2!B3 then copy down and accross:
``Please Login or Register  to view this content.``
Confirm with holding both Ctrl-Shift, then hit Enter

Or

``Please Login or Register  to view this content.``
Enter only.

3. ## Re: Find UPC code from material numbers and populate 3 bracket prices

I don't know if this is what you want but it appears like the sheet2 that you are referring to is a table in sheet1. I inserted a column A and used the mid function to extract the upc code and colored the font white to hide the extracted number then used the vlookup function to populate the table sheet2.

4. ## Re: Find UPC code from material numbers and populate 3 bracket prices

Wow that works, awesome, thank you.

Is There a way for it to search all 3 material numbers (since upc could match one of three material numbers, as long as the 5 digits that i enter is anywhere within any 3 material numers) and return all 3 bracket prices if match is found within one of 3 material numbers?

5. ## Re: Find UPC code from material numbers and populate 3 bracket prices

Thank you both formulas work, but both formulas work for bracket 1 price. How can I make these formulas to also show bracket 2 and bracket 3?

And can they also search all 3 material numbers?

6. ## Re: Find UPC code from material numbers and populate 3 bracket prices

I don't understand why it has to search all 3 material numbers, when the UPC code is the same in all three. The the attached does fill all 3 brackets and 0 if not found.

7. ## Re: Find UPC code from material numbers and populate 3 bracket prices

So attached is my actual spreadsheet, I used the formulas recommended here and it works.

ActualExample.xls

What would help me now is if on Sheet2 i can somehow group together same "Bracket 1 Unit Prices" and add together their Bracket 1 Unit Totals

Example
.97 - 139.68
.97 - 104.76
.97 - 139.68 = 384.12 (and maybe even list one upc of that group with the total)

1.99 - 15.92 = 15.92 ( in other instances there would be multiple 1.99 items just like above )

2.34 - 2.34 = 2.34 ( in other instances there would be multiple 2.34 items just like above )

prices are not always gonna be in order like they are right now, but will be all over.
Example:
.97
.97.
1.99
.97
1.99
2.34
.97
1.99
1.99
.97
2.34

is that possible?

8. ## Re: Find UPC code from material numbers and populate 3 bracket prices

This might work for you, click the 1 2 and 3 buttons on the left side of the sheet2.

9. ## Re: Find UPC code from material numbers and populate 3 bracket prices

Originally Posted by Dunc3142
This might work for you, click the 1 2 and 3 buttons on the left side of the sheet2.
I really dont understand how to do that or how to manipulate it. If you can maybe explain it to me.

What Im really looking for is what i have in the spreadsheet attached, it just needs to be auto populated, I can even input all different prices manually if it can pull same prices into groups.

Calculations2.xls

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1