## Equivalent of VLOOKUP in DAX–Part I

One frequent DAX requirement is to write a formula that behaves like the VLOOKUP function in Excel.

Gehrard Brueckl recently blogged about how to map a date to a fiscal period, when fiscal periods are defined in a separate table, and each period is defined by its start date. Ken Puls recently wrote an article about calculating an effective tax rate with DAX.

In this first post, I will present the LOOKUPVALUE function.

VLOOKUP, a classic Excel function, accepts two values (TRUE or FALSE) for its last argument.

Assuming we defined names for Value, Range, i, the formula will look like this:
=VLOOKUP(Value, Range, i, FALSE)

VLOOKUP will look for the first occurrence of Value in the first column of Range. If the value cannot be found, the formula will return #NA, else it will return the value in the i-th column on the same row.

Note that VLOOKUP also works with arrays.

When the last parameter is TRUE (or omitted), then VLOOKUP requires your data to be sorted in ascending order. It will look for the last value in the first column of Range that is not greater than Value, and will return the value in the i-th column on the same row, or #NA if none exists.

More details on VLOOKUP here, there, or everywhere on the internet.

# LOOKUPVALUE

## Syntax

DAX V2 introduced a LOOKUPVALUE, that has a related behavior.

A notable difference is that LOOKUPVALUE allows you to provide criteria on several columns. In Excel formulas, this would require you to use concatenate columns, or even replace VLOOKUP with some INDEX/MATCH formula.

The syntax looks like this:
= LOOKUPVALUE( Table[OutputColumn], Table[LookupColumn1], “SearchValue1”,  Table[LookupColumn1],  “SearchValue2”)

The syntax looks like the vector form of the LOOKUP function, if you put the order of arguments aside. It actually looks like the syntax of the LOOKUPS function, which does not actually exist.

Basically, you first have to define which column contains your output, then define your first lookup column, then provide the value you are looking for, then define your second lookup column, and so on …

## Behavior

LOOKUPVALUE behaves differently from VLOOKUP:

1. whereas VLOOKUP works with arrays, LOOKUPVALUE does not work with column expressions. In other words, the columns arguments must reference columns that physically exist in your model. Also, remember that each column must belong to the same table.
2. if several rows in your table match your criteria, then VLOOKUP in Excel will only return the first (or last) match. On the contrary, LOOKUPVALUE in DAX may return an error if several rows match your criteria. This will occur if [OutputColumn] does not contain the same value for all matching rows.
3. a BLANK value is returned if there is no match.

Also, not surprisingly, you must address columns by names and cannot use indexes to do this.

## When to use LOOKUP value?

When your lookup table is a parameter of your model. By that, I mean a static table that you can import in your model.

Obviously, when the behavior described above suits your requirements.

When you want to calculate search values on the fly, or use different search values that come from different tables. In other words, when you cannot use PowerPivot relationships.

### 12 Responses to “Equivalent of VLOOKUP in DAX–Part I”

1. fbrossard Says:

Hi Laurent,
Great post.
For my part, I often use LOOPVALUE in two main uses cases :
1. to implement parent-child hierachies with calculated columns
2. to define filter on the fly for my measures (very useful for time calculation such as week to date, year-over-year calculations, etc …)
Fred.

2. TheDataSpecialist Says:

Hi Fred,

thanks for the comment.

Laurent.

3. […] Equivalent of VLOOKUP in DAX–Part I […]

4. DataMonkey Says:

Is there a way to use Lookup or some other function in DAX to do a “like” or a % match? For instance if the base table has a long string and the lookup table has the substring that I want to match, is there a way to use Lookup?

• TheDataSpecialist Says:

In I present a pattern mimicking VLOOKUP’s behaviour. The functions like FIND / SEARCH and LEFT / RIGHT / MID allow you to check whether a string contains another one.

• I’m not sure I got the link from “In I present a pattern…”. Was there a link that was missed out there? Also, I understand that FIND/SEARCH and the other functions mentioned help to look for a single string in another string. But in this case I need LOOKUP’s iterative power to do that FIND on each of the lookup strings and return the first match. That isn’t clear from this post or your comment. Any ideas on how I could achieve this with LOOKUP or any other DAX function?

• TheDataSpecialist Says:

Indeed, my previous reply contained a link to part II of this “series”. As far as I know LOOKUPVALUE does not accept wildcards.

Here is a quick summary of the approach I describe in part II:
– iterate over a table with the FILTER function (using the appropriate text search function) to determine which rows match your pattern.
– use TOPN to make sure the expression only returns one row
– use this row as context argument of a CALCULATE expression – get the value you want with the VALUES function:
CALCULATE( VALUES( ResultColumn] ), TheRowExpression )
where TheRowExpression is a TOPN(FILTER( … )) expression.

5. niri Says:

Is there any way to find first occurrence of a value where it does present in all the months -using DAX?

6. niri Says:

No, want to find the first month of each customer(say) where the customer appeared for the first time

7. Kasia Says:

I like your blog it is well organised and article i just studied was written in beginner DAX language, nice. Thank you