This article is about LOOKUPVALUE function, which assigns values from another table. There doesn't´t have to be a relationship between these tables.

How to use it?

In this example we need to connect data from these two tables. One of them contains info about sales, but we miss the prices of items. The prices are in the second table and we will use the LOOKUPVALUE to assign them.

So we need to add the proper price from Pricelist table to every row in Sales table..

Let´s type this into a new column:

  • Price of item = LOOKUPVALUE(
       Pricelist[Price per unit];
       Pricelist[Item name];
       Sales[Item];
       123)

Because:

  • Price from pricelist is a name of new column
  • Pricelist[Price] is a column in the "second" table, that contains the values we need
  • Pricelist[Item] is the column in a "second" table, containing the "key".
  • Sales[Item]) is the column in the same table we are typing, that contains the key.
  • 0 is an optional parameter. Declares the result for situations, where no equal value can be found - something like IFERROR.

The prices are added:

Few notes:

  • Its principle is very similar to VLOOKUP (but LOOKUPVALUE works in DAX, when VLOOKUP works in common Excel).
  • Also RELATED from DAX is has similar logic. It si easier then LOOKUPVALUE to create, but needs a relation between tables.
  • If LOOKUPVALUE finds multiple relevant values to assign, it generates error.
  • LOOKUPVALUE can use multiple columns as a key. For example if you need to assign prices based on the combination of Month and Item, it works like this.

11 Comments

  1. what is lookup, what is the use how it works i need clarity with examples

  2. Related needs you have create a relationship (one to more) between the two tables you are considering.
    Lookupvalues instead allows you to do the same without create a relationship.

  3. how can i look up last quantity sold of a product in price table from sales table?

  4. Need your help in doing this. I have 2 tables and i want Actuals against budget in the budget table, and Budget against actuals on the actual table using LOOKUPVALUE
    CCC Nominal Month Budget
    50001 88033 01/04/2020 100
    50002 88034 01/04/2020 200

    CCC Nominal Month Actuals
    50001 88033 01/04/2020 200
    50002 88034 01/04/2020 200

  5. It depends. If you want to assign values based on CCC only, then the syntax is:
    =lookupvalue(budgets[Budget];
    budgets[CCC];
    actuals[CCC])
    This creates a calculated column in Actuals, showing relevant Budget value.
    If you want to use all calumns for assigning, then syntax is like this:
    =lookupvalue(budgets[Budget];
    budgets[CCC];
    actuals[CCC];
    budgets[Nominal];
    actuals[Nominal];
    budgets[Month];
    actuals[Month])
    Look at this:
    https://exceltown.com/wp-content/uploads/lookupvalue-2.png

  6. Hi, Thanks for the solution. However it work on 2 rows only, i have 80k lines and this show me an error – A table of multiple values was supplied where a single value was expected . Does more rows with multiple dates, nominal and CCC will not work?

  7. And if there are multiple values meeting the criterias, what should happen? Do you want to summarize them, count them, average them?…

Leave a Reply

Your email address will not be published.

*

clear formPost comment