# OFFSET double lookup in PowerPivot

March 8, 2012 7 Comments

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:

## SELECT Band, [Group], Units FROM

## (SELECT BandA, BandB, BandC, [Group] FROM Table1) t

## UNPIVOT

## (Units FOR Band IN (BandA, BandB, BandC)) as unpvt

## 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:

## DoubleLookup:=IF ( HASONEVALUE( Data[Band] ) ,

## CALCULATE(

## LASTNONBLANK( Lookup[Group], 1) ,

## FILTER( Lookup,

## Lookup[Band] = VALUES(Data[Band] ) &&

## Lookup[Units] < VALUES( Data[Units] )

## )

## )

## )

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/

Pingback: “VLOOKUP, OFFSET and MATCH in PowerPivot” from Javier Guillén « VLOOKUP WEEK

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.

Hi, can you post a sample of your data?

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

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

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

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.