“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

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