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, Data Analysis Expressions (DAX) functions don’t take a cell or cell range as a reference—as VLOOKUP does in Excel. DAX functions only take a column or a table as a reference.
In Power Pivot, remember that you're 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 for analyzing 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 lookup or formula is necessary. This simple solution does have some requirements: The lookup column Region must be in a related table, and the Geography table cannot be hidden from the PivotTable Field List.
There are a number of cases in Power Pivot in which you might want to lookup values in another table as part of a calculation—such as a calculated column or measure. These require a custom formula created using DAX, which includes several functions that perform similar lookups. The one that you choose depends on what you need 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.
