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.

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.

• There the key in the table with results is not unique, you get an error by default. To get any value in this case, you may use something like this instad of LOOKUVALUE:
• CALCULATE(
MIN(resulttable[resultcolumn]),
FILTER(resulttable,resulttable[keycolumn]=thistable[keycolumn]

1. lazar says:

Good, but what if you have 2 or 3 prices for every item?

2. Banu says:

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

3. Banu says:

what is the difference between lookup and Related

4. Ivan says:

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.

5. sajid says:

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

6. Dianne says:

Can you use LOOKUPVALUE for text values

7. Jiří Beran says:

Yes, why not?

8. Srikanth says:

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

9. Jiří Beran says:

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:

10. Srikanth says:

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?

11. Jiří Beran says:

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

12. Srikanth says:

I have a calendar year starting from Apr and ending Mar – Can some one help in writing a DAX – attached is the table and expected results
FY Calendar (Table)
Month FY Calendar
1/4/19 2018-19
1/5/19 2018-19
1/6/19 2018-19
1/7/19 2018-19
1/8/19 2018-19
1/9/19 2018-19
1/10/19 2018-19
1/11/19 2018-19
1/12/19 2018-19
1/1/20 2018-19
1/2/20 2018-19
1/3/20 2018-19

Cost table
Month Cost YTD
1/4/19 100 100
1/5/19 100 200
1/6/19 100 300
1/7/19 100 400
1/8/19 100 500
1/9/19 100 600
1/10/19 100 700
1/11/19 100 800
1/12/19 100 900
1/1/20 100 1000
1/2/20 100 1100
1/3/20 100 1200

13. Zuzka says:

Hi, I need help with the lookupvalue. I am solving similar task as the second example with the pricelist with candies. Just the pricelist is not based on months but the price is valid in time interval from-to (two columns) and I need to fill the approprite price valid at the date of selling the item to the second table. Please what would be the syntax then?

14. Jiří Beran says:
15. Srikanth says:

Is there a way to attach files

16. Srikanth says:

Actual \$ 50 Budget \$ 100 and variance \$ 50 and % variance is 50% – working fine with below formula
Actual \$100 Budget \$0 and variance \$100, ideally the % diff should be 100% but i am getting no values

Formula used
DAX formula % Var Cost = DIVIDE([Var_Cost],[Bud_Cost],””)

17. Serhan says:

its not same with excel vlookup, because it doesnt bring the first value that it found. If the table has lot of same values lookupvalue doesnt bring the first value . its a problem about it, and no solution I guess, right?

18. Serhan says:

Yes it works like excel, i reply myself. but why it says “While waiting for a single value, a table of multiple values was provided” when i do lookupvalue. I concatenate mont()&year() of date , and usermail for both table and then I set up lookupvalue but it doesnt work.
But if I concatenate just date and usermail, it works. there are multiple similar items on both table by the way.

19. HASSAN NAZEER says:

How to allocate Total Amount till its 0
Decending Order
EX;
Total Paid : \$500

Invoice :
Jan 1 INV00001 \$100
Jan 6 INV 000025 \$200
Feb 8 INV000058 \$400

So want to apply 500 Dollars to INV 000058 & 25
Want result as

Total Paid : \$500
Jan 6 INV 000025 \$100
Feb 8 INV000058 \$400

20. Haider says:

Hi , I need to know how do do a vlook from text columns. i have 2 tables in BI both in text format