In over my head - populate from another sheet?

[rdraper]

HI - thanks for reading. Im not a excel newbie, more of a mid beginner and Im in way over my head here.

Ok . My current worksheet (worksheet 1) i have a whole year of sales data from invoicing. It is around 17000 lines. A mixture of product numbers from various vendors throughout the year - alot of them repeated throughout Column G.

- Column D - has product decription
- Column E - date of sale
- Column G - has product #'s.
- Column H - the vendor name
- Column I - qty
- Column J - Price (which is blank on sheet 1)

On worksheet 2 i have the pricing sheet from the vendors listing their product #'s and pricing (for simplicitys sake i have copied into Column A and B on worksheet 2)

I would like to be able to populate somehow the pricing on worksheet 1 Column J for the product # in Column G (with the pricing being on worksheet 2)

Is this possible?

Thanks in advance - my brain hurts just thinking of how to accomplish

 

[AlphaFrog]

Could you use a VLOOKUP or INDEX-MATCH formula?

In J1 put this...
=VLOOKUP(G1, Sheet2!A:B, 2, 0)

Or...
=INDEX(Sheet2!B:B, MATCH(G1, Sheet2!A:A, 0))

 

[rdraper]

Thanks AlphaFrog for your reply/help.

I tried both formula's in J4 (my data starts in line 4) and I changed the formula to G4 in both formulas. The result was the same - "#N/A"

Sheet 2 (i literally named it Sheet 2) has all the product #'s for 5 different vendors in Column A and the prices in Column B, but the product #'s vary, so they are only in order for their perspective company (if that makes sense).
When I tried the VLOOKUP formula, Excel help mentioned something that the data it is looking up needs to be in ascending order? Since WS 2 has various vendors and the part #'s are different - i wasnt sure if that mattered.

None the less - Im still a no-go thus far.

 

[AlphaFrog]

Excel help mentioned something that the data it is looking up needs to be in ascending order? ...i wasnt sure if that mattered.

Not in this case. We are searching for an exact match which doesn't require the data to be sorted.

If you have a space within your sheet name e.g. Sheet 2 (note the space between t and 2), then you need to surround the sheet name with single quotes
=VLOOKUP(G4, 'Sheet 2'!A:B, 2, 0)
Or...
=INDEX('Sheet 2'!B:B, MATCH(G4, 'Sheet 2'!A:A, 0))

Can you post your exact J4 formula?

 

[rdraper]

There isnt a space in my sheet name - it is Sheet2
so, my formula is

=VLOOKUP(G4, Sheet2!A:B, 2, 0)

 

[rdraper]

I think I found the culprit.
The formula worked but the first product # in line G4 is NOT listed in my product price list on Sheet2 - thus it couldnt find a value. When I dragged the formula down the line it found the pricing for the products listed.

Thanks again - It was alot easier than i made it out to be

 

[rdraper]

Ok, I think that is the correct formula yet when I drag it down I am getting alot of "#N/A" results. I believe alot of them are stemming from in my Sheet 2, column A (product #'s) there may be a space at the beginning of the number. Although it doesnt look like there is, when i place cursor at beginning of the product # and hit delete (even though it didn't appear to delete anything) - when i went back to my main sheet - the price appeared. I suspect that 60% of my product codes on Sheet 2 have this "secret space" at the beginning of the product code. Is there a way to remove all of them at once? Kind of like a Find/replace for a blank space?

Thanks again

 

[AlphaFrog]

Try this. (It might not work)...

• Select the product codes column
• Select from the menu Data\ Text to Columns
  • Step 1 of 3: select Delimited and click Next
  • Step 2 of 3: uncheck all delimiters and click Next
  • Step 3 of 3: Column data format:General and click Finish

 

[rdraper]

Alpha Frog - Thanks, that worked on some those product #'s yet I have one of the vendors (there are 7 total) that arent pulling across until i retype it in. Let me better explain.

On my main sheet on this one particular vendor I still get "#N/A" in column J. I verified the product #'s in both column G and also in Column A (on Sheet 2). They are both formatted as general and the number is correct.
On Sheet 2, if i clear the # and just retype it, then it populates on my main sheet.
Any clue why Sheet 2, Column A still has issues (and only with this vendor)