Microsoft Excel has many powerful formulas that allow you to ease the existential burden of working in excel sheets. Dare I say, they make it fun (but mostly just effective).
This is your basic Vlookup formula:
=Vlook(A1, C$2:D$10, 2, 0)
Let's break that down into smaller, more digestible parts.
=Vlookup is the start of the formula.
A1 is the search criterion. This is what the formula looking for in the array, based on the set column.
C$2:D$10 is the array you are searching. Using a '$' between column and row fixes the array to just those columns/rows.
2 is the column in the array that you want to pull the data from. Depending on how big your array is, this can range from 1-X, where X is the farthest right column in the array you set.
0 is you telling the formula you want EXACT matches only. Always use '0' here.
Now let's show what it can do. Here, we are going to transfer the UPCs from column E to column B.
Notice how the SKUs in column D do not match the same order as column A. The Vlookup formula does not care about this, and that's what makes it so powerful.
This is the formula we will use, pasting it to B2.
=Vlookup(A2, D$2:E$6, 2, 0)
This is how the formula translates to the excel sheet.
Once you've entered the Vlookup formula in B2, click on the lower right hand corner of the cell and drag the formula down.
And voila, the formula pulls the UPC from column E and inserts it into column B, tied to the correct SKU.
But we are not done quite yet! There's one final important step to take.
When uploading templates into LM, it is important to remove any formulas you were using. You can do this via Special Pasting. Select all the formulas (in this case, it'd be B2-B6) and CTRL +SHIFT + V. This will paste the VALUES of the formula, and not the formula itself.
Article is closed for comments.