«

»

Create an Offset Vlookup

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 be solved

We have a table (column A:D ) that lists the sales of our products for each month. To gain in visibility , we want to reorder our values ​​in 2 tables (columns G to J) for displaying the sales and the number of product sold per month.

offset_lookup_1

As you can see, in column A, many dates are missing. But that is not the issue. Even if we copy dates in the empty cells, we are facing the same problem; we can not identify the data for the products B, C and D.

VLOOKUP or INDEX

The idea is to find the position of dates (which is our ID) and read the data that is below by shifting by 1, 2 or 3 lines. In fact, the VLOOKUP function is not conveninent here. VLOOKUP is perfect to retrieve the values that are on the same row but not to perform an offset because VLOOKUP returns a value and we need to return a range.

Therefore, we must use the INDEX function to build our research because the INDEX function returns a data 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. For further explanations for the INDEX function, you can consult the page INDEX function and also the article about the MATCH function.

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

offset_lookup_2

The formula can be understood as follows: We are interested in the data range A2:D49 (our data without the header) and we are looking for the line corresponding to the date we are interested in (MATCH function). After we indicate that we retrieve the 3rd column (parameter 3) to return the number of items sold.

To return the sales for the product A, we just have to change the value of the column and replace it by 4.

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

offset_lookup_3

Create an offset lookup

As the INDEX function returns a data range (not a value as does the VLOOKUP), we will include in the 2 previous formulas in the OFFSET function.

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

The OFFSET function returns a Data compared to a pivot cell. In our example, to return the number of product B, we need to shift from one cell down compared to the previous search. We will write our formula as follows for the product B.

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

offset_lookup_4

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

//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js

1 comment

  1. Anonymous

    Can you please give a link to a data file download for this exercise.

Leave a Reply

%d bloggers like this: