# OFFSET double lookup in PowerPivot

One of those really great functions in the native Excel environment is OFFSET.  In combination with other functions like MATCH, it can generate a type of ‘double lookup’ that can be really helpful when creating dynamic reports.  For example, the table below describes a matrix defining a coordinate of units for each combination of ‘Band’ and ‘Group’.

We want to use this table as a lookup reference when generating matches for the following values:

Take for example, the first row.   We first look at the ‘Band’ value which is B in this case and find it in our lookup table.  Then we go down the rows and, using an approximate match logic, find the group for which the value of 120 belongs to.   That would be Group 3 (which has unit values going from 70 to 129).

In Excel, we can use a formula like the one below to generate this ‘double lookup’ calculation:

VLOOKUP(A11,

OFFSET(\$A\$1,1,MATCH(B11,\$A\$1:\$D\$1)-1,4, 4),(4 – MATCH(B11,\$A\$1:\$C\$1)) + 1,TRUE)

This formula finds the appropriate columns first (column B) and then uses an approximate match VLOOKUP to find the row (#4) and column ( D ) to retrieve the right group.  The final result is:

Pretty powerful.

If we are analyzing data with PowerPivot, we want to be able to generate the same effect within the PowerPivot itself without having to drop all data into Excel and do the OFFSET lookup there. This could be because we are scanning millions of rows or simply because we want to keep all calculations in the same place (the PowerPivot database)

In order to do this we need to first understand one thing: we must unpivot the lookup table so we can successfully generate the lookup in DAX.   Unlike what you can do with the OFFSET function – in which you can specify a column / row coordinate dynamically – in DAX we will be only filtering rows.

If our data is in a database like SQL Server, we can do this by executing the following T-SQL expression:

#### ORDER BY Band, [Group], Units

The result will be the following:

In the case you are relying on data which resides only on your spreadsheet, you can use the ‘Multiple Consolidation Ranges’ wizard to generate the same effect.  This wizard is found by going to File –> Options –> Customize Ribbon.  On the ‘Choose Commands From’ dropdown, select ‘All Commands’ and then find and add to your ribbon the ‘Pivot Table and Pivot Chart Wizard’.  Finally, click on the wizard button we just added to the ribbon.  You should see the following window:

Notice that I moved the ‘Group’ column to the left. This is because the wizard expects the categories on the first column. Select ‘Multiple consolidation ranges’ and follow the instructions.  Once complete, you will get a pivot table using the data from our lookup table. Move the ‘Row’ and ‘Column’ fields to the row labels.  Finally, flatten the table out (using the PivotTable Options context menu) and you will end up with the following result:

Now you are ready to import the unpivoted lookup data into the PowerPivot window.  Be sure to change the column names to something more meaningful than ‘Column’, ‘Row’ and ‘Total’.  In my case, I have called them ‘Band’, Group’ and ‘Units’.

Finally, import the items that we are going to be using against the lookup (2nd screenshot on this blog entry) – I called it ‘Data’. Notice that this is yet another scenario in which there is no actual relationships between the tables in the data model. All the work will be done using a DAX expression:

#### )

Notice the expression is very readable and shows how a fairly simple DAX formula can have a powerful dynamic effect. LASTNONBLANK selects the last [Group] value on a filter context for the current Band where the lookup units are less than the current unit value. The output matches the more complex expression we defined using OFFSET, MATCH and VLOOKUP:

The formula works even if we have repeated ‘Band’ values.  In that case, I suggest adding an identity column to the table in order to break down the resulting value accordingly -

Output:

This post has been featured on Excel’s MVP Bill Jelen “VLOOKUP Week” (March 25 – 31, 2012 ) which groups podcasts and blogs describing the use of this and other interesting Excel-related lookup functions and techniques. For more information, visit Microsoft’s Excel team blog @ http://tinyurl.com/c2aw89y , or directly access VLOOKUP’s week website @ http://vlookupweek.wordpress.com/

### 7 Responses to OFFSET double lookup in PowerPivot

1. Mohan NC says:

Hi, I am a novice to Powerpivot. I am looking for the equivalents of Index/Match functions in Powerpivot. I have list of several hundred employees with their salary placement (Lane and Step). The value based on Lane and Step should be picked from a salary matrix (The top row consists of Lane fields AA, BA, MA, Phd and the 1st column consists of Steps 1 to 35). So, for example , if an employee is BA/Step-5, I should have the “Salary” field pick up the value from the matrix. how do I do this in Powerpivot? I greatly appreciate your help.

2. Mohan NC says:

Thank you javier for your help.
Here is the sample employee data:

Emp Name Lane Step Salary
Sarah BA 1
Michael AA 5
Robert PHD 8
Richard MA 4

Here is the sample matrix
STEP AA BA MA PHD
0 10,000 10,350 10,712 11,087
1 10,325 10,686 11,060 11,448
2 10,661 11,034 11,420 11,820
3 11,007 11,392 11,791 12,204
4 11,365 11,763 12,174 12,600
5 11,734 12,145 12,570 13,010
6 12,115 12,540 12,978 13,433
7 12,509 12,947 13,400 13,869
8 12,916 13,368 13,836 14,320
9 13,336 13,802 14,285 14,785

• javierguillen says:

In a similar way to the one described on this blog post, you can use the ‘Multiple Consolidation Ranges’ Wizard in Excel to ‘unpivot’ your lookup table. This is because PowerPivot lookup’s are based on rows primarly, not columns. The result you get is the following:

Lane Step Salary
AA 0 10000
AA 1 10325
AA 2 10661
AA 3 11007
AA 4 11365
AA 5 11734
AA 6 12115
AA 7 12509
AA 8 12916
AA 9 13336
BA 0 10350
BA 1 10686
BA 2 11034
BA 3 11392
BA 4 11763
BA 5 12145
BA 6 12540
BA 7 12947
BA 8 13368
BA 9 13802
MA 0 10712
MA 1 11060
MA 2 11420
MA 3 11791
MA 4 12174
MA 5 12570
MA 6 12978
MA 7 13400
MA 8 13836
MA 9 14285
PHD 0 11087
PHD 1 11448
PHD 2 11820
PHD 3 12204
PHD 4 12600
PHD 5 13010
PHD 6 13433
PHD 7 13869
PHD 8 14320
PHD 9 14785

I called this ‘SalaryLookup’ on the PowerPivot model. Then I imported your Employee table as well. With that, a DAX calculated column will take care of finding the right value:

Calculate (
Values ( SalaryLookup[Salary] ),
Filter ( SalaryLookup, SalaryLookup[Lane] = Employee[Lane] && SalaryLookup[Step] = Employee[Step] )
)

The result is correct based on a cross-reference of Lane and Step:

EmpName Lane Step Salary
Sarah BA 1 10,686
Michael AA 5 11,734
Robert PHD 8 14,320
Richard MA 4 12,174

Hope that helps

3. Mohan NC says:

Thank you so much Javier. Your prompt help is greatly appreciated.

4. javierguillen says:

Sure! Doing the equivalent of ‘VLOOKUPs with multiple matching criteria’ is easy in PowerPivot; the thing to keep in mind is the way data must be structured. Vertical Lookups are always preferred, rather than a matrix that relies on horizontal lookups as well. In a way, this ‘limitation’ also keeps data more tidy, and easier to manage.