By Dany Hoter

We always think about a data model with multiple tables and relationships between them as a big improvement over the common practice of combining tables using VLOOKUP expressions.
(Watch Video for more: PowerPivot Relationships are EASIER than VLOOKUP, not just faster)

I came across a customer request that forced me to admit that in some aspects a VLOOKUP is more flexible than relationships. To solve some specific scenarios we have to use modeling methods which are not elegant and not efficient.


VLOOKUP better than Relationships?

I hope that some of the readers will know to defend the cause of the data model and find a magic solution that is both elegant and efficient.

How can good old VLOOKUP be better?

A solution based on VLOOKUP is using a separate formula for every row, this is what makes it cumbersome, slow and error prone – right?

Yes but this is also what makes it more flexible in some cases.

Let’s describe some reasons why is it more flexible and give some examples:

VLOOKUP can use a different way to find the right row based on the input value

=IF(A5=””
, VLOOKUP (A6,lookuptable6,3,false)
, VLOOKUP (A5,lookuptable5,4,false))

If lookup value is empty in our table, there is no point in looking for it in the lookup table and instead a different lookup table is used and a different lookup value is searched for.

For example (download), if a state column is empty or contains “NA”, return a value from the countries table using the country as a search value.


VLOOKUP gives us the flexibility to lookup State or Country names

How would you solve this in a Power Pivot data model? Create a calculated column in one table that contains the state or the country and create a relationship with a table that contains both countries and states. Or let the relationship fail to find a matching row in the lookup table and apply the solution from the next paragraph.

The expression can make a decision on a row by row basis how to behave in cases the VLOOKUP doesn’t find a matching row.

IFERROR(VLOOKUP(A5,lookuptable5,3,false),”Other product”)

IFERROR(VLOOKUP(A5,lookuptable,3,false),A5)

IFERROR(VLOOKUP(A5,lookuptable5,3,false),VLOOKUP(A6,lookuptable6,4,false))

I can decide what to show for every column that is based on VLOOKUP in case it is not found.

In the first example I show a constant value.

In the second example I show the key itself

In the third case I perform another VLOOKUP if the first one fails.

What is the equivalent behavior of a relationship?

Any value that is coming from the 1 side of a relationship and is not found in the 1 side returns as blank().


(blank) values when using Lookup Tables
can cramp your style

This is not always friendly to the consumer of a report.

It may be expected that not all values in the many side are not found in the 1 side but blank is not always the best option to show instead.

What can we do to solve the problem

Exactly what we are trained not to do – create a calculated column in the many side.

Assume you have a column in the products table called [Product Name]. The table is on the 1 side of a relationship with the sales table.

You can create a column called [Product Name] in the sales table with the DAX expression

=IF(ISBLANK(RELATED(Product[Product Name])
,”Other Product”
,RELATED(Product[Product Name]))

Now we should hide the [Product Name] column from the products table and use the one in the sales table.

Elegant? – no

Efficient? – no

But this is the best solution I can find.

In the included Excel file I used both the VLOOKUP solution and the DAX solution in the same table

So I wouldn’t be seen stupid I ran this problem through one of the leading experts and he agreed that it is a real scenario so now I propose this as a challenge to both the experts and the team who owns the modeling experience.

Download All Files:
Example1 Example2 (Excel 2013)
Example (Excel 2010 Version)