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)?

So you're suggesting to create a new column with entries like: "1,Z1,2014Q3" - 1,Z2,2014Q3" etc., then use some INDEX(MATCH) approach? Can I communicate with the PowerPivot environment though commands like that? Wouldn't it need a table/column name somewhere? ('data'[Column]) — Jerros, Nov 27 '15 at 14:01
Problem is, even that one column would be larger than Excel can handle, hence the PowerPivot way of just "putting in in your RAM" is neat for this data. But that means I can't refer to it other than by using cube functions, which lose out on some of the functionality DAX commands do offer... — Jerros, Nov 29 '15 at 13:09

score 0 · Answer 1 · answered Nov 27 '15 at 22:36

0

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:

answered Nov 27 '15 at 22:36

Gary's Student

95,722
10
59
99

This is in the normal Excel sheet right? What if the table exists in PowerPivot environment - then things like CubeVALUE (which does roughly the same as you describe) won't work for strings anymore... If the table would be in an Excel sheet already then there wouldn't be any problem. thing is, it sits in the PowerPivot environment. – Jerros Nov 29 '15 at 13:11

score 0 · Accepted Answer · answered Nov 28 '15 at 04:28

0

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.

answered Nov 28 '15 at 04:28

greggyb

3,728
1
11
32

This sounds great - thanks! I'll try it tomorrow. I had used LOOKUPVALUE in a measure before, but I couldn't think of a way to change the input values I'd given it in the PowerPivot environment, thus it would only ever return one and the same value. – Jerros Nov 29 '15 at 13:16
Works brilliantly. The "HASONEVALUE" check can even be omitted - any combination of person, time and tekstCode is unique per definition in this dataset. No need to create a pivot table either - just the measure with the "VALUES" command did the trick. I thought one had to put actual data-values as an argument, but replacing that with the VALUES input like you suggested works like a charm already, thank you! – Jerros Nov 30 '15 at 08:49
As a followup - the LOOKUPVALUE outputs strings, which can also consist of numeric characters only. If the latter's the case, I'd like the output to be numeric and not a string. I have tried `IF(ISNUMBER(LOOKUPVALUE(Input Statement)*1);1;0)` but anything like that errors... (as a sidenote - when one of the measures error, all other working measures give errors as well!?) Playing around with the `VALUE` also yields no success... – Jerros Nov 30 '15 at 11:16
The HASONEVALUE() checks are defensive against users, not data. Based on your requirement, we know that the three fields form a primary key for this lookup. If you distribute this model to end users, they might accidentally or ignorantly make a nonsensical selection that includes more than one distinct value. In that case, an error would be thrown. Rather than allow the error, we use the IF(HASONEVALUE()) to just BLANK the measure - this is cleaner and less scary to end users. – greggyb Nov 30 '15 at 15:18
As for formatting, if you're only using a single field pivot, it may be easier to hide the pivot and use Excel formatting and formulas with a reference to that pivot – greggyb Nov 30 '15 at 15:18

Oceanopticon · Answer 3 · 2016-07-01T18:45:41.893

0

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]}}")

edited Jul 01 '16 at 18:45

answered Jun 29 '16 at 22:38

Oceanopticon

56
4

So you're basically doing three lookups and multiply the result such that only a non-zero result would show right? Isn't this slow? (if you'd do this in the regular Excel environment with array lookups, it becomes excruciatingly slow) Also, would this still work with strings? In my experience, only numerical results can be recalled this way... I'll try to find out though, thank you! – Jerros Jul 01 '16 at 06:57
Right. I'm not sure how well it performs at larger scales. There may be a more efficient way to write it - I am a total MDX novice and just found a syntax that works. And yes, it works with strings. – Oceanopticon Jul 01 '16 at 18:55

LOOKUPVALUE function in worksheet?

3 Answers3