VLOOKUP Functionality in Power Pivot

One of the most popular functions in Excel formulas is VLOOKUP. But, you can't use VLOOKUP in Power Pivot. This is primarily because in Power Pivot, DAX functions don't take a cell or cell range as a reference like VLOOKUP does in Excel. DAX functions take a column or a table as a reference. Keep in-mind, in Power Pivot, you are working with a relational data model. Looking up values in another table is really quite easy, and in many cases you don't need to create any formula at all.

For example, let's say you have a PivotTable in an Excel worksheet to analyze sales data in your data model, and you want to slice based on regional data from a Region column in a Geography table. You can simply add Region as a field to the PivotTable. No particular lookup or formula is necessary. This simple solution does have some requirements: The lookup column Region must be in a related table, and Region or the Geography table it's in cannot be hidden from the PivotTable Field List.

There are a number of cases in Power Pivot where you might want to lookup values in another table as part of a calculation, like in a calculated column or measure. These require a custom formula created using Data Analysis Expressions (DAX). DAX includes several functions that perform similar lookups. Which one you use will depend on what you want your formula to do.

What do you want to do?

Use the RELATED function to lookup values in a related table.

Use the RELATEDTABLE function to lookup a table with all rows related to the current row.

Use the LOOKUPVALUE function to return values by filter criteria.