Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.

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:

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

It will work just fine:

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. 
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.