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 to retrieve the values that are on the same row but not to perform 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.
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 simply change the argument of the column and replace it by 4
Create the Offset
Because the INDEX function returns a data range (and not a value like VLOOKUP), we will include in the 2 previous formulas the OFFSET function.
=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:
And so on for the other cells. For product C, the formula will be
And for product D