«

»

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 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.

offset_lookup_1

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.

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

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).

offset_lookup_2

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 👍😎😍

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

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:

=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

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/create-an-offset-vlookup/


Leave a Reply

Your email address will not be published. Required fields are marked *