How To

VLookup

VLookup is an immensely useful tool that many people don't know how to use. You'll find a lot about them if you google. But basically, a vlookup is a way to grab data from one set of values based on a matching data point in another set of values. For instance, say you want to make sure the new ads you want to upload use the same category as their live siblings, so that the variation listings will post without an error. You can export all the Child SKUs and their Parent field for the full list of Parent SKUs represented by your new ads. Then export the existing ads with their Category and SKU, and use VLookups first to match the Parent SKUs to the ads, and then to match the existing categories to your new Child SKUs using the Parent that you just inserted.

In this example, first we’re going to get the Parent SKUs for a set of ads. And then we’ll get the Category number for the Child SKUs that share the same Parent.

The first column of your named table needs to be the data that you want to match on. So for our first example, to get the Parent SKUs for the ads, we’re going to match on SKU (Inventory Number). Because that’s the data point that both sets of data share. To name a table, first select the data you’ll be using. Here we start with Inventory Number as the first column because that’s the data we’ll be matching on:

With the data selected, click into the Name Box next to the fx button and type a name for your table:

Hit Enter, and your data will be in a named table:

Now that you have a name, you can use that in a VLookup to get the Parent SKU for your ads based on the ad’s SKU. The formula is = VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup]):

  • lookup_value is the cell coordinate of the data you’re matching on – in this case, SKU
  • table_array is the name of your table – in this case, parent
  • col_num_index is the column number within your table array that has the value you want to get – in this case, 2, because Variation Parent SKU is the 2nd column in our table
  • range_lookup is a logical value that specifies whether you want to find an exact match or an approximate match. You want True, because that will return an exact match.

So for this example, our formula reads: =VLOOKUP(C2,parent,2,false) because the first SKU we want to match on is in Column C, row 2:

Our formula will look in the first column of the table named “parent” for the value in C2, and will return the value in the second column of the table for the first match it comes to. As you drag down, it’ll check the value in each cell and return the matching value from the “parent” table:

Now that we have our Parent SKUs associated with our Ads, we can create a second named table and get the Category for our new SKUs. First we name our table, selecting the values from the Parent SKUs through the Category column and then typing a name in the Name Box like we did before. The names must all be unique, so this time we’ll use “catnum” for category number:

Now we can use a VLookup formula to populate the Category column for our new SKUs:

This time, our formula reads =VLOOKUP(G2,catnum,4,false) because we’re matching on Parent, which is in column G2, and the value we want to return, Category, is in the 4th column of the new named table that we made called catnum. Now as we drag the formula down, it checks each value in column G for a corresponding value in our table and returns the value in the 4th column of the table for the first match it comes to:

It’s important to remember that a VLookup will only return the value for the first match. So for example, if you have a list of SKUs that you want to get the Retail Price for but the data you’re matching on has variations in the retail price, you may not get the results you want. In this example, you’ll see that it’s returning the lowest Retail price in each case because the table is sorted with retail ascending:

If we re-sort the “retail” table to put the highest retail value at the top, our results change:

VLookup is an immensely useful tool that many people don't know how to use. You'll find a lot about them if you google. But basically, a vlookup is a way to grab data from one set of values based on a matching data point in another set of values. For instance, say you want to make sure the new ads you want to upload use the same category as their live siblings, so that the variation listings will post without an error. You can export all the Child SKUs and their Parent field for the full list of Parent SKUs represented by your new ads. Then export the existing ads with their Category and SKU, and use VLookups first to match the Parent SKUs to the ads, and then to match the existing categories to your new Child SKUs using the Parent that you just inserted.

In this example, first we’re going to get the Parent SKUs for a set of ads. And then we’ll get the Category number for the Child SKUs that share the same Parent.

The first column of your named table needs to be the data that you want to match on. So for our first example, to get the Parent SKUs for the ads, we’re going to match on SKU (Inventory Number). Because that’s the data point that both sets of data share. To name a table, first select the data you’ll be using. Here we start with Inventory Number as the first column because that’s the data we’ll be matching on:

With the data selected, click into the Name Box next to the fx button and type a name for your table:

Hit Enter, and your data will be in a named table:

Now that you have a name, you can use that in a VLookup to get the Parent SKU for your ads based on the ad’s SKU. The formula is = VLOOKUP (lookup_value,table_array,col_index_num,[range_lookup]):

  • lookup_value is the cell coordinate of the data you’re matching on – in this case, SKU
  • table_array is the name of your table – in this case, parent
  • col_num_index is the column number within your table array that has the value you want to get – in this case, 2, because Variation Parent SKU is the 2nd column in our table
  • range_lookup is a logical value that specifies whether you want to find an exact match or an approximate match. You want True, because that will return an exact match.

So for this example, our formula reads: =VLOOKUP(C2,parent,2,false) because the first SKU we want to match on is in Column C, row 2:

Our formula will look in the first column of the table named “parent” for the value in C2, and will return the value in the second column of the table for the first match it comes to. As you drag down, it’ll check the value in each cell and return the matching value from the “parent” table:

Now that we have our Parent SKUs associated with our Ads, we can create a second named table and get the Category for our new SKUs. First we name our table, selecting the values from the Parent SKUs through the Category column and then typing a name in the Name Box like we did before. The names must all be unique, so this time we’ll use “catnum” for category number:

Now we can use a VLookup formula to populate the Category column for our new SKUs:

This time, our formula reads =VLOOKUP(G2,catnum,4,false) because we’re matching on Parent, which is in column G2, and the value we want to return, Category, is in the 4th column of the new named table that we made called catnum. Now as we drag the formula down, it checks each value in column G for a corresponding value in our table and returns the value in the 4th column of the table for the first match it comes to:

It’s important to remember that a VLookup will only return the value for the first match. So for example, if you have a list of SKUs that you want to get the Retail Price for but the data you’re matching on has variations in the retail price, you may not get the results you want. In this example, you’ll see that it’s returning the lowest Retail price in each case because the table is sorted with retail ascending:

If we re-sort the “retail” table to put the highest retail value at the top, our results change:

0
Your rating: None
0
Your rating: None