Excel Magic Trick 1137: Excel & PowerPivot DAX Formula: Approximate Match Lookup Without VLOOKUP

See how to
1) (00:08 min) Intro.
2) (01:21 min) Approximate Match with VLOOKUP.
3) (02:20 min) Approximate Match with AGGREGATE (to simulate logic that is present in DAX formula).
4) (05:11 min) Approximate Match Lookup in PowerPivot DAX using CALCULATE, MAX, FILTER and Boolean Logic.

[excelisfun]

Excel Magic Trick 1137: Excel & PowerPivot DAX Formula: Approximate Match Lookup Without VLOOKUP 
See how to 
1) (00:08 min) Intro.
2) (01:21 min) Approximate Match with VLOOKUP.
3) (02:20 min) Approximate Match with AGGREGATE (to simulate logic that is present in DAX formula).
4) (05:11 min) Approximate Match Lookup in PowerPivot DAX using CALCULATE, MAX, FILTER and Boolean Logic.

[saiwa]

This is WILD!!! sounds like a totally different language to me. Can wait to see your power pivot series!!!

[MiscaXL]

Love the AGGREGATE trick & looking forward to the PowerPivot videos!

[excelfan]

Haha cant wait for the series. I really enjoy your explanations and appreciate your willingness to shout out to others, your generosity is evident in all you videos and your comments thanks again

[mikeburton]

Excellent, cannot wait for your PowerPivot series, you make it look easy!!

[DougHExcel]

Wow😀...great example of approximating a lookup with power pivot!

[pmsocho]

Great video! I love the AGGREGATE function solution :)

[MikeDolanFliss]

Wow, I am SO looking forward to this series.  Thanks Mike!

[himanshudalai]

Excellent learning !! THANK YOU MIKE for this awesome video !!

[EVUTube]

I'm quite intrigued by PowerPivot and am studying it myself.  Would be so nice to bring some "ExcelIsFun" magic to that learning process.

[excelisfun]

@Amey Dabholker

Microsoft made it REALLY hard to get PowerPivot. You have to have either:

1) Office Profesisonal Plus
2) Excel 2013 Stand Alone
3) Office 365, E3 or E4 editions.

There may be other ways to get it, but those are the ways that I know.

[ExcelxlNl]

Great video and introduction to PP. Thanks

[frumoasapaduri]

You Are a Life Saver! Keep it up and I might get the "Job"

[russelljf]

It's a shame that excel doesn't autocorrect function names in title rows as it does in formula creation... VLOOUP ;-)
nice to see what power pivot is all about, been meaning to find out... Seems to be quite a hybrid of Excel and Access.

[PeterVandivier]

I was going crazy trying to figure out this logic all weekend!! Can I share this video with myself from last Friday?

[excelisfun]

@Carlos Barboza, I do not have a playlist yet.

[KaneCharlesFoster]

The AGGREGATE solution is a nice one. Is there a way to have it work when the column I want the data from does not contain numbers? Someting like If sales are from 0$ to 100$ I want to write "Bad seller" from 100$ to 500$ "Averege seller" from 500$ to 1000 "Good seller" and above 1000 "Great seller".

[saidkhalidscharaf]

Fantastic explanations! You make it really easy to get this rather complicated topic. I just noticed that in the file "PowerPivot10-CALCULATE-Start.xlsx", on the sheet "CC vs. CF", in the cell B5, you said "If you have the choice between drag and drop to SUM in a Pivot (called implicit formula) and making a Calculated Field with SUM function (called Explicit formula), it is better to use implcit because of "Portable" concept.". Should this not say "...use explicit..."?

[marounsader]

hi dear please would you do this for text lookup.
some did it with hasonevalue but im not getting how it works! i appreciate doing one for hasonevalue value and how to approximate text

[ameydabholkar]

How you got the powerpivot. You have excel 2013 and powerpivot. . I am searching from when that how to get powerpivot for office 365 2013.. please help me to get it, ...