In the case you have a table that contains the same ID for many - or empty 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 the 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 a data in a range.
For the formula in G2, we will write the following formula to return the number of items sold in January 2014 for the product A.
The formula can be understood as follows
- We are focused on the data A2:D49 (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).
Extract the sales
To return the sales for the product A, we just have to change the value 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 a data based on the reference of a pivot 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. If you double-click one of the cells below, you will display the formulas in the corresponding cells