Let say, you have a table that contains the same ID for many cells. It is strongly recommended to reorder your data

To perform this modification, we will use 3 functions, INDEX, MATCH, and OFFSET.

Problem to solve

We have a table (column A:D ) with the list of sales for each product.

We want to reorder our values ​​in 2 different tables automatically. So we are going to create formulas between the 2 finals tables and the initial table.

Look! There is a BIIIIG issue. 😱😱😱In column A, many dates are missing. Even if we copy dates for the empty cells, we have a problem. We can not point the data for products B, C, and D.

VLOOKUP or INDEX

The idea is to find the position of the dates (our ID).

Then, we will read the next value by shifting by 1, 2 or 3 rows. The VLOOKUP function is not convenient here. VLOOKUP is perfect for retrieving the values that are on the same row but not for performing an offset.

Therefore, we must use the INDEX function to build our research because the INDEX function returns data in a range by position (and not values)

For the formula in G2, we will write the following formula to return the number of items sold in January 2014 for product A.

=INDEX($A$2:$D$17,MATCH($G2,$A$2:$A$17,0),3)

The formula can be understood as follows

• We are focused on the data A2:D17 (the data without the header)
• Then we look for the row corresponding to the date we are interested in (MATCH function).
• To finish, we indicate the value 3, the column index to return the number of sales.

Extract the sales

To return the sales for product A, we change the argument of the column and replace it by 4

=INDEX($A$2:$D$17,MATCH($G10,$A$2:$A$17,0),4)

Create the Offset

Because the INDEX function returns a data range (and not a value like VLOOKUP), we will include the OFFSET function in the 2 previous formulas.

=OFFSET(reference, number of rows, number of columns)

The OFFSET function returns data based on the reference of a pivot cell (the initial cell).

In our example, if we want to return the quantity of product B, we need to shift from one cell down compared to the previous search.

The formula for the product B is:

=OFFSET(INDEX($A$2:$D$49,MATCH($F2,$A$2:$A$49,0),3),1,0)

And so on for the other cells. For product C, the formula will be

=OFFSET(INDEX($A$2:$D$49,MATCH($F2,$A$2:$A$49,0),3),2,0)

And for product D

=OFFSET(INDEX($A$2:$D$49,MATCH($F2,$A$2:$A$49,0),3),3,0)