Home > Articles > Computer Software > Business Office Software > Spreadsheet Software > Microsoft Excel

  • Print
  • + Share This
From the author of Using the RELATED Function in the PowerPivot Grid

Using the RELATED Function in the PowerPivot Grid

Once you have defined a relationship between two worksheets, you can refer to the other worksheet when building formulas in the PowerPivot grid. The RELATED function is a simpler way to do VLOOKUP. Say that you wanted to calculate sales per square foot. Revenue is in the Demo worksheet and Square Feet is in the StoreInfo worksheet. In Regular Excel, you might build something like this:

=E2/VLOOKUP(A2,StoreInfo!$A$2:$B$144,2,False)

In the PowerPivot grid, the formula becomes this:

=demo[Revenue]/Related(StoreInfo[Selling SF])

With VLOOKUP, you had to define the lookup value, the table, which column in the table to return, and specify FALSE to show that you wanted an exact match. The RELATED function does the same lookup by telling PowerPivot to follow the existing defined relationship to get the Selling SF field from the StoreInfo table. PowerPivot figures out that it has to use the Store Number field in both tables to return the correct lookup value.

  • + Share This
  • 🔖 Save To Your Account