Tag Archives: PowerPivot

“VLOOKUP, OFFSET and MATCH in PowerPivot” from Javier Guillén

In this example, Javier is simulating (in PowerPivot) the output achieved through double lookups using VLOOKUP, OFFSET and MATCH.

One of those really great functions in the native Excel environment is OFFSET.  In combination with other functions like MATCH, it can generate a type of ‘double lookup’ that can be really helpful when creating dynamic reports.  For example, the table below describes a matrix defining a coordinate of units for each combination of ‘Band’ and ‘Group’. …To Read More Click Here to go to Javier Guillén’s Lesson

You may find Javier on Twitter @javiguillen or at his blog Javier Guillén on WordPress

Advertisements

Javier Guillén “Simulates an “approximate match” VLOOKUP in PowerPivot”

Javier’s first entry begins:

“It is often said that relationships in PowerPivot work in a similar way to VLOOKUPs.  In reality, this is only partially true. If we examine the claim a bit closer we realize that Excel’s VLOOKUP function has a parameter in which it is possible to use an approximation when matching values against the lookup table…”  To Read More Click Here to go to Javier Guillén’s Lesson

 

You may find Javier on Twitter @javiguillen or at his blog Javier Guillén on WordPress


Rob Collie from PowerPivotPro: Does PowerPivot make VLOOKUP Obsolete?

The first time that I saw PowerPivot create a pivot table from two worksheets WITHOUT doing any VLOOKUPs, I realized that a lot of people who thought VLOOKUP was scary would now be able to do some very powerful analyses. My favorite blog on PowerPivot is run by former Excel Project Manager Rob Collie. This morning, at 12:04AM, Rob checked in with a blog post that offers two amazing techniques:

  1. Joining two tables in PowerPivot using the Create Relationship dialog box
  2. Once you’ve joined two tables, using the DAX function =RELATED in the PowerPivot grid to replace VLOOKUP

Read Rob’s post, VLOOKUP Week – Who Needs VLOOKUPs Anymore.