LOOKUPVALUE
Returns one value from a column, found by matching one or more columns to given values, with no relationship needed.
LOOKUPVALUE(<result_columnName>, <search_columnName>, <search_value> [, <search_columnName>, <search_value>]... [, <alternateResult>])Returns: A scalar value. Blank, or the alternate result, when no row matches.
What it does
LOOKUPVALUE finds the row whose search columns match the values you give, and returns a value from the result column. Use it when no relationship exists between the tables, or when the match needs more than one column. It works like an Excel lookup with several keys. If a relationship exists, RELATED is simpler and usually faster.
Syntax and arguments
- result column: the column whose value you want back.
- search column and search value (one or more pairs): the columns to match and the values to match them to. The search value can be a single value or a column in the current row.
- alternate result (optional, last argument): returned when no row matches. Without it, the result is blank.
- The search must match one row. If several rows match, the function returns an error.
Example
The standard list price of the product selected in a slicer. This assumes product names are unique in the Product table.
Standard Price =
LOOKUPVALUE (
Product[UnitPrice],
Product[ProductName], SELECTEDVALUE ( Product[ProductName] )
)
If one product is selected, the price for that product is returned. If nothing or several products are selected, the search has no single value, so the result is blank.
Example: a second use
A rate lookup by two keys, currency and month, from a rates table that is not linked to the model. The rate defaults to 1 when no match is found. This assumes a rates table with Currency, Month and Rate columns, which is not part of the Contoso sample.
Rate Applied =
LOOKUPVALUE (
FxRates[Rate],
FxRates[Currency], Budget[Currency],
FxRates[Month], Budget[Month],
1
)
This is a calculated column on the Budget table. Each budget row looks up its own currency and month. The final 1 is the alternate result.
Things to watch
- Several matching rows cause an error. Make sure the search keys identify one row.
- A missing match returns blank, unless you give an alternate. Blanks then flow into totals and can hide a gap.
- It searches the table on each call, so it is slower than
RELATEDon large tables. - Keys must match in data type and spelling.
- Prefer a relationship when the model can have one.
In business terms
Adding a standard price, an exchange rate, an approved budget rate or an account group to a list that is not in the model. Month-end workbooks that bring in a separate rates file rely on this kind of lookup.