LOOKUPVALUE
A column's value in the row whose search columns have the values given; when none has, or rows disagree, an alternate result if given, else BLANK or an error.
Group: Filter. Compatible with DAX.
Syntax
LOOKUPVALUE ( <result_columnName>, <search_columnName>, <search_value> [, <search2_columnName>, <search2_value>] … [, <alternateResult>] )| Parameter | Type | Description |
|---|---|---|
result_columnName | Column | The column whose value is wanted. |
search_columnName | Column | The column to look in. |
search_value | Value | The value the search column must have. |
search2_columnName | Column optional, repeatable | Another column to search in. |
search2_value | Value optional, repeatable | The value that column must have. |
alternateResult | Value optional | What to return when no row matches, or the matching rows disagree. |
Returns
Same type as the result column. The result column's value in the rows that match every pair.
Remarks
- It searches the whole table, whatever the filter context: a filter on Products does not hide the row it finds.
- No matching row gives BLANK, or the alternate result when there is one.
- Several matching rows with the same value give that value. With different values it is an error, or the alternate result when there is one.
- A search column may also come from a table the result column's table looks up through a relationship, on its one side: a row of Sales is found by its product's name or its shop's district (example 2). A column of an unrelated table is an error.
- Between tables joined by a relationship,
RELATEDin a row context is simpler and quicker.
Examples
One search pair, then two
EVALUATE
ROW (
"IPA's price", LOOKUPVALUE ( Products[Price per liter], Products[Product], "IPA" ),
"IPA in Oslo",
LOOKUPVALUE ( Production[Margin per liter], Production[Product], "IPA", Production[Plant], "Oslo" )
)| IPA's price | IPA in Oslo |
|---|---|
| 64 | 19.5 |
Search columns of related tables
A month of Sales found by the district of its shop and the name of its product, both in other tables.
EVALUATE
ROW (
"Pils at Frogner, October 2026",
LOOKUPVALUE ( Sales[Liters], Sales[Month], DATE ( 2026, 10, 1 ), Places[District], "Frogner", Products[Product], "Pils" )
)| Pils at Frogner, October 2026 |
|---|
| 3195 |
No match, and matches that disagree
No product is called Stout. IPA is made at three plants with three margins, so without a plant the alternate result 0 comes back.
EVALUATE
ROW (
"Stout", LOOKUPVALUE ( Products[Price per liter], Products[Product], "Stout" ),
"Stout, else -1", LOOKUPVALUE ( Products[Price per liter], Products[Product], "Stout", -1 ),
"IPA at any plant, else 0", LOOKUPVALUE ( Production[Margin per liter], Production[Product], "IPA", 0 )
)| Stout | Stout, else -1 | IPA at any plant, else 0 |
|---|---|---|
| (blank) | -1 | 0 |