Die deutsche Fassung finden Sie hier.

If you've had a look at formulas that can be used to map data from one sheet to another before, you might have come across the VLOOKUP formula. It's great in cases where the data you want to map is okay to be manipulated (for VLOOKUP the search criteria must be in front and the list needs to be sorted by search criteria).

But sometimes it is impossible to edit the data from the mapped table to make it usable with VLOOKUP, because the table would become unusable doing just that. In order to be able to make an assignment anyway, we make use of INDEX and MATCH.

We start by having a look at the function call for the INDEX formula,

=INDEX(array; row_num; column_num if applicable)

Since the formula only returns a value from inside of an array, but does not search in it, we have to combine the formula with another formula. Here MATCH comes into play,

=MATCH(lookup_value; lookup_array; match_type)

The formulas parameters speak quite for themselves. So we can have a look at how the formulas intertwine:

=INDEX( column that we want to return;MATCH(current table[search criterion]; mapped table[search criterion];0))

 EXAMPLE

In this example we'll use INDEX(MATCH) to map EAN codes from a supplier list to our products in cobby. First we open our supplier data in Excel, then we load our products in cobby. Now we select the upper most cell that we want to fill with data from our suppliers list, in this case the cell is the first empty entry under EAN (the attribute had just been created in Magento and after a quick re-index we can now fill it with data) and start writing our formula in the formula bar.

We start with =INDEX(

Then we switch to our supplier sheet and click right on the column header, whose data we want to output. In this case column C.

Now we enter a semicolon ( ; ) followed by the MATCH( formula. The first parameter for the match formula is the search criterion on the cobby sheet (in our case the SKUs match with the supplier).

The second parameter tells the MATCH what it should compare against. In our case we use the first column in the supplier list since it matches our SKUs.

After another semicolon ( ; ) we tell the formula to look for an exact match. The constant for that is 0.

Two closing brackets )) build the end of our formula. Now that the formula creation is done we can double-click on the lower right corner of the cell to apply it to the whole column.

Did this answer your question?