Vlookup issue

[justanot]

Hi, I have been trying to get this to work for days now. I feel like it must be easy for some of you, so I'm reaching out for help. Any advice is greatly appreciated...

There are 3 files involved. We are talking products and inventory/pricing. I need to be able to update inventory every two days, but the file I receive every two days does not contain all the fields that I need for the upload, so I have to combine them in a certain way. Sorry if I confused you already. Let me elaborate:

First file is the original product file containing

• SKU
• UPC
• PRICE
• QTY

This is the master file used to create products on a selling platform.


Second file is the file I receive every two days containing:

• SKU
• UPC
• QTY

This file is sent to me for accurate inventory levels. This is the file that changes every day due to adjustments in stock.

Third file is the file I need populated every two days with the following fields:

• SKU
• UPC
• PRICE
• QTY

This file is needed to load accurate inventory into selling platform. I am unable to refresh inventory on the selling platform without populating SKU, UPC, PRICE and QTY. All fields are required.

How do I take items from the FIRST file, look up the SKU or UPC value in file number TWO and then have it return new QUANTITY from file #two into file number ONE?

Does this make sense? I even confused myself at this point...

[TMS]

I'd have thought you needed to base your new file on the second file with the current SKU list in case any are added or removed or the UPC is changed.

So, I'd copy file 2, making it file 3 (for upload) and add a VLOOKUP formula to get the price from file 1.

Alternatively, just copy file 1 and make it file 3. Then replace the existing quantity values with a VLOOKUP to get the new quantity field from file 2.

I think either approach could work but I don't know enough about your processes to be definitive.

Regards, TMS

[justanot]

Alternatively, just copy file 1 and make it file 3. Then replace the existing quantity values with a VLOOKUP to get the new quantity field from file 2.

Hi TMS,

thank you so much for taking the time to read and respond. I am interested in the alternative option you provided. If I was to copy file 1 and make it file 3, and look up quantity from file 2, what would be the vlookup formula? Let me attach a sample for you to take a look at. I tried several vlookups but was never able to get correct result.

Thanks in advance.

[tacape]

Look at the attached. I basically copied file 1 and pasted the SKU's and UPC's codes. Then I did a vlookup in the pricing column and quantity.

[TMS]

The easy way is to right click on the tab for File 1 and choose "Move or copy..." and tick the "Create a copy" box. I'd select "Move to end".

Double click on the tab for the new copy and rename it as File 3. You now have an exact copy of the sheet File 1 called File 3.

On the new sheet File 3, put this formula in cell D2: =VLOOKUP($A2,'File 2'!$A:$C,3,FALSE) ... then copy down to the last row. This looks for the SKU in column A on the sheet File 3 in the sheet File 2, column A and, if there's a match, returns column 3 ... the updated quantity.

If you did decide to copy File 2 and look up the price in File 3, the formula would be: =VLOOKUP($A2,'File 1'!$A:$D,3,FALSE). In fact, as you're not interested in the quantity, it could just be: =VLOOKUP($A2,'File 1'!$A:$C,3,FALSE) ... spot the difference?

Hope this helps.

Regards, TMS

[JetKaren]

Tacape, if I had to work with this information, I would simply update the qty value in File 1 with the qty data from File 2 with a VLOOKUP then perform a file save as... to save File 1 as File 3.

Here is the VLOOKUP I would use to update the QTY field in File 1:

=VLOOKUP(A2,'File 2'!A:C,3,FALSE)

This is referencing the SKU field, if you wanted to use the UPC field, your VLOOKUP would be this:

=VLOOKUP(B2,'File 2'!B:C,2,FALSE)


Good luck!

[TMS]

@JetKaren: tacape isn't the OP. It doesn't really matter whether you put the VLOOKUP in File 1 or make a copy and put it in File 3 ... it's the same formula looking at the same data in File 2. I have made a copy because that's what the OP said he wanted ... presumably so that he can track before and after quantities.

Regards, TMS

[justanot]

Hi everyone,

I was able to get what I needed. Thank you all so much, you saved me a ton of time and some brain cells.

Quick question, why is the Table Array A:C? I get the $ reference, but why A : C? Can it just be A since the sku is in column A?

[TMS]

Thanks for the rep.

A is the column you are looking up; C is the column with the value you want to return; hence, A:C,3,FALSE

Regards, TMS

[Cutter]

@ justanot

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

[justanot]

Hi, I was actually trying to mark it as solved yesterday but could not find how. All I had under Thread Options was: Show Printable Version and Unsubscribe from this thread. Now that you marked it as solved, I see other options. Thanks.