Menu

How to use Vlookup to add Brands to Amazon Product Ads reports

By Rory Witt    |    June 16, 2015

FacebookTwitterPinterest

Sometimes it is much easier to make a proper analyze of the effective campaign for each brand if your brands are linked with the products, for now Amazon do not show it automatically, but you can easily do it manually.

Here the steps to follow:

1) Download yo ur Amazon Report and Product List.

2) Now you have two txt files, just copy the data to Excel.

If files are too big (over 200mb) it can be difficult to edit it via standard tools, these text editors can help you:

010editor (http://www.sweetscape.com/010editor/), it can open giant files and allow you to edit and search the text.

Notpad++ ( http://notepad-plus-plus.org/download/v6.7.5.html) simple and good editor to use, it has 2GB limit.

3) In the tab Products list you would need two columns ”“ id and brand. Copy them to the left side of your Amazon Report.

4) Add a new column Brand in your Report tab ”“ it is the place where you want the Brand to be filled in.

5) In the first cell of your new column Brand, press Function button and search for VLOOKUP

Vlookup_1

6) At the Excel’s Function Arguments dialog you have to set the values.

Lookup_value ”“ this is your starting point, the ID for which you want to find the Brand

Table_array ”“ a range for your lookup table, where we should look for the ID

Col_index_num ”“ this is the number of the column on the lookup table that has the information we need. In our case we need to get the information that is in second column

Range-lookup – this defines how close a match should be between Lookup_value (ID) and the value in the lookup table. In our case, we want an exact match so we need to use “FALSE”.

For the table_array you should set symbol – $ ($M$2:$N$15), then you can easily drag the formula to other cells.

Vlookup_2

7) This is how it should look like.

Vlookup_3

Once it’s done you can copy/past the newly created Brand column and save just text data, after you can remove id and brand column for the tab.

Leave a Reply

Your email address will not be published. Required fields are marked *

Focus On Your Business

For the rest, we’ve got you covered.

Contact us

Hello!