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
, 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