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.

# Quick notes about VLOOKUP

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:

- 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. - 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. **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.

February 21, 2013 at 11:58 pm

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.

February 22, 2013 at 11:07 am

Hi Fred,

thanks for the comment.

I would love to hear/read more about point 1.

Laurent.

March 4, 2013 at 2:15 pm

Hi Laurent,

To implement Parent/Child hierachies, the first step is to create a calculated column to retrieve for each rows the entire path such as :

‘TableName’[HierarchyPath] = PATH([NodeId], [ParentNodeID])

And then you have to create a calculated column for each level of your hierachy using the LOOKUPVALUE such as :

‘TableName’[Level1]= LOOKUPVALUE ([Node value to show], [NodeId], PATHITEM ([HierarchyPath], 1))

…

‘TableName’[LevelN]= LOOKUPVALUE ([Node value to show], [NodeId], PATHITEM ([HierarchyPath], N))

Here are two references on the subject :

. Kasper de Jonge : http://www.powerpivotblog.nl/powerpivot-denali-parent-child-using-dax

. Alberto Ferrari : http://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/19/parent-child-hierarchies-in-tabular-with-denali.aspx

See U.

Fred.

February 22, 2013 at 2:21 pm

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

May 1, 2013 at 12:36 am

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?

May 1, 2013 at 11:24 am

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.

May 3, 2013 at 5:21 am

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?

May 3, 2013 at 12:31 pm

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.