2 min
Updated: Aug 31, 2020
There was a time when VLOOKUP was undoubtedly one of the most used functions in Excel. Knowing VLOOKUP used to be a yardstick of expertise in Excel. During my various training sessions, it was an easily most sought-after topic among the participants.
LOOKUPVALUE is the DAX counterpart of VLOOKUP. It is a useful and straightforward DAX. It comes handy, especially when there is no relationship between the tables in the data model.
In the case of an existing relationship, we can use RELATED and RELATEDTABLE. You may follow this article for RELATED and RELATEDTABLE.
Let us start with a simple example.
LOOKUPVALUE syntax has three required arguments
<RESULT_ColumnName>
The name of the column we need as an output, in case of a match. In this case, it is Name (dtEmp)
<Search_ColumnName>
The name of the column, in the same table as <Result_ColumnName> over which we are performing a look-up. In this case, it is EmpID(dtEmp).
<Search_Value>
The look-up value, what we are searching for in the search table. It can be a column or a scaler value. In this case, it is EmpID (dtScore)
We write the following calculated column:
Emp Name =
LOOKUPVALUE(dtEmp[Name],dtEmp[EmpID],dtScore[EmpID])
Kind of like of VLOOKUP!
LOOKUPVAULE offers more flexibility than VLOOKUP: we can match more than one condition.
We will write the following calculated column:
Score =
LOOKUPVALUE(
//Result column
dtScore[Score],
//Condition 1
dtScore[EmpID], dtEmp[EmpID],
//Condition 2
dtScore[Month],dtEmp[Month]
)