Search

Create a Lookup Offset

Reading Time: 2 minutes

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.

Lookup with offset

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.

=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.
Number of sales for product A

Extract the sales

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

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

Amount of sales for Product A

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:

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

Offset of one cell down after the lookup

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)

All data are transfered to the tables

Related posts

Search in a range of values with XLOOKUP

Frédéric LE GUEN

Anonymise your data

Frédéric LE GUEN

VLOOKUP on the left in Excel

Frédéric LE GUEN

1 comment

Peter 10/03/2019 at 06:09

Thank you! Works perfectly.

Reply

Leave a Comment