This article will explain how to construct an offset VLOOKUP. That is, how to return values ​​that are not on the same line as the desired value.

Problem to solve

In case you have a table that contains identifiers common to several rows, or empty cells to indicate that the identifier is repeated, it is strongly recommended to re-order your data.

To achieve this, we will use 3 functions, INDEX, MATCH and OFFSET

We have a table (column A:D) which lists the sales of our items at the end of each month. To improve readability, we want to reorder our values ​​in the 2 tables in columns G to J to display the sales and turnover per month.

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

Solution with Power Query

The method described below applied when Power Query did not exist. From, without the slightest formula, You can get the same result just in a few steps

Construction of the formula

The idea is to find the position of the dates (which is our identifier) ​​and read the data below by shifting 1, 2 or 3 lines.

Ne pas use the VLOOKUP function

Indeed, the VLOOKUP function is perfect for retrieving values ​​that are on the same line as the identifier.

But for the other lines it is impossible because the VLOOKUP function returns a value and not a range.

Prefer the INDEX function

We must then base ourselves on the function INDEX to build our research because the INDEX function returns a range of data.

For formula G2, we will write the following formula to return the number of items sold in January 2014 for product A. For the detailed explanations about the INDEX function, you can consult INDEX function and to the MATCH function.

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

The function can be understood as follows:

• We are interested in the data range A2:D49 (our data without the header line)
• We search for the line corresponding to the date that interests us (EQUIV function).
• After we indicate that we retrieve the data from the 3rd column (parameter 3) to return the number of items sold.

If we want to return the turnover, we specify 4 for the last parameter of the INDEX function.

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

Do an offbeat search

As the INDEX function returns a range of data (and not a value as the VLOOKUP function does), we will be able to include the 2 previous formulas in a SHIFT function.

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

The OFFSET function returns data relative to a pivot cell.

In our example, to return the number of articles B, we must shift one cell down from the previous search. We will therefore write our final formula as follows for product B:

=DECALER(INDEX($A$2:$D$49,EQUIV($F2,$A$2:$A$46,0),3),1,0)

And by copying this formula, the final result is: