Search code examples
excelpowerbipowerpivot

How to do a range index and match or vlookup in power pivot excel?

These are the individual data points of the data model:

enter image description here

These are the monthly salaries of the employees (obtained using the pivot table from the data model's data):

enter image description here

Each cell will then be used as the Lookup value which will be run through a table.

The lookup value is to be looked up in column A and column B of the table below and if it is matched (within the range), it will return the corresponding value under column C.

enter image description here

I am unable to find any index and match or vlookup functions in the power pivot functionality of excel using measures--which are used to get some analytics on aggregated values on report objects such as pivot tables.

I have found LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…) which is a DAX function however, the issue here is that I am doing a range lookup and as shown below, I don't know if you can have an array as an argument to the function. enter image description here

Traditional calculated fields also do not allow arrays in the formulas.

Lookupvalue() only works on a single column lookups because it will return an empty cell if it cannot find a match as shown below:

enter image description here

But when it does find a match using the table below:

enter image description here

It will work just fine:

enter image description here

Solution

  • First, you need to create a measure for Pay:

    Total Pay = SUM(Table1[Pay])

    It's important to do it as a measure instead of just dropping 'Pay' into a pivot table (this is called an 'implicit measure' and is concidered a bad practice).

    Then, let's say your table with pay ranges is named "Pay Ranges". Create another measure:

    Returned Value = 
CALCULATE( 
VALUES('Pay Ranges'[Value To Return]), 
FILTER( 'Pay Ranges', 
[Total Pay] >= 'Pay Ranges'[Lower Bound] &&
[Total Pay] <  'Pay Ranges'[Upper Bound]
))

    Make sure that all these formulas are Measures, not calculated columns. Also, the formula relies on the correct construction of the ranges. If they overlap, you will get an error.