LOOKUPVALUE function in worksheet?

Question

In Excel PowerPivot, I can use a simple DAX function to search for a value inside the database using LOOKUPVALUE like this:

LOOKUPVALUE('data'[Value];'data'[Person#];2;'data'[TekstCode];"Z2";'data'[Time];"2014Q4")  

This will return an output (string, number or whatever) matching a person#, a code-column and a time - which would be Lol in this case, with the table below:

TABLE: data

Person# TekstCode   Time    Value

1       Z1          2014Q4  Hi
1       Z2          2014Q4  Bye
2       Z1          2014Q4  3
2       Z2          2014Q4  Lol
3       Z1          2014Q4  45
3       Z2          2014Q4  twenty20
1       Z1          2015    Hi2
1       Z2          2015    Bye3
2       Z1          2015    44
2       Z2          2015    Lal
3       Z1          2015    45
3       Z2          2015    thirty30

I would very much like to use this functionality OUTSIDE of the PowerPivot environment (which allows for DAX), and just put this formula in a regular worksheet so I can refer to cells for the input for LOOKUPVALUE.

Note that using the CUBEVALUE function (which can be used in a regular worksheet) is not an option - it can't return strings. Also, having PowerPivot create a PivotTable is no option, as the table contains almost 100-million records and even if a table of such size could be created, speed would still be an issue.

I like the speed of how for example CUBEVALUE pulls a single element from the giant database and want to use something like LOOKUPVALUE to quickly pull elements from the database. By inputting three values in a column (I input a person#, a TekstCode, a Time - which in a worksheet I can put in some cells and refer to in the formula, so it would be easy to pull-through the formula) the function spits out the matching text string.

    A   B   C       D
1   1   z2  2015    =LOOKUPVALUE('data'[Value];'data'[Person#];A1;'data'[TekstCode];B1;'data'[Time];C1)
2   3   z1  2014Q4  =LOOKUPVALUE('data'[Value];'data'[Person#];A2;'data'[TekstCode];B2;'data'[Time];C2)

--->

    A   B   C       D
1   1   z2  2015    Bye3
2   3   z1  2014Q4  45

How can I use this functionality in a normal worksheet (a.k.a. outside of the DAX environment)?

Answer 1

You can use the CUBEMEMBER function instead of CUBEVALUE to pull a value from a column in PowerPivot. You just need to figure out the MDX syntax (which for me personally, is not easy).

It would look something like this:

=CUBEMEMBER("ThisWorkbookDataModel","[data].[Person#].&[2]*{[data].[TekstCode].&[z2]*{[data].[Time].&[2014Q4]*[data].[Value].[Value]}}")

Then you can add in cell references from your workbook to make it dynamic like this:

=CUBEMEMBER("ThisWorkbookDataModel","[data].[Person#].&["&A1&"]*{[data].[TekstCode].&["&B1"&]*{[data].[Time].&["&C1&"]*[data].[Value].[Value]}}")

Answer 2

Use LOOKUPVALUE() in a measure, with the search values controlled by filters on the appropriate columns. Below is a simple example.

LookupMeasure:=
IF(
    HASONEVALUE( Data[Person#] )
        && HASONEVALUE( Data[TekstCode] )
        && HASONEVALUE( Data[Time] )
    ,LOOKUPVALUE(
        Data[Value]
        ,Data[Person#]
        ,VALUES( Data[Person#] )
        ,Data[TekstCode]
        ,VALUES( Data[TekstCode] )
        ,Data[Time]
        ,VALUES( Data[Time] )
    )
    ,BLANK()
)

You'll need three slicers or three filters based on the lookup fields connected to a pivot table. That pivot table should have no row or column labels, and should have [LookupMeasure] in its values section.

We test with the IF() whether each filter has exactly one unique value. When that's true, we evaluate the LOOKUPVALUE() based on those selections. Otherwise we return blank. You can position this pivot table anywhere you need it, or reference its value in any cell.

Answer 3

If you have a table-like structure and want to apply multiple criteria and then retrieve a value based on the criteria, you can use SUMPRODUCT() to find the row and the INDEX() to get the value. For example, to get the Name of a medium, black, cat: