If Vs Vlookup Vs Xlookup - Which One Is Best For Commission?

[@matteozurli269]

Great Video John! I think many people still tend to use nested if because it's how our brain works thinking about a problem like this one (eg IF the sales amount is between x and y then etc. IF false etc.), people should try always to look at the problem from a "Formula maintenance" perspective and use VLOOKUP or XLOOKUP. Thanks for sharing!

[@ExcelCampus]

Thanks Matteo! Great point about our brain being logical and operating in IF statements. 👍

[@agnieszkajedrzejewska-wnuk641]

Very good video with a good example and useful explanation!

[@wayneedmondson1065]

Hi Jon.. thanks for the lookup fun with VLOOKUP and XLOOKUP. Of course, we can't forget our old friend INDEX and MATCH, as in: =INDEX(D4:D7,MATCH(C9,B4:B7,1)). You could also make VLOOKUP more bulletproof with CHOOSE, as in: =VLOOKUP(C9,CHOOSE({1,2},B4:B7,D4:D7),2). If you have access to Microsoft 365, XLOOKUP is the way to go.. don't even need to have sorted lookup_array for it to work on an approximate match. Always good tips/tricks and learning at your channel :)) Thumbs up!!

[@ExcelCampus]

Hi Wayne, Thanks so much for the nice feedback! INDEX & MATCH is another great alternative. Thanks for mentioning that. I forgot to mention additional alternatives in the video. I tend to avoid using CHOOSE in VLOOKUP because it can slow down the calc speed if you have a lot of formulas in your file. Here is a video on a performance test with VLOOKUP(CHOOSE). kzbin.info/www/bejne/ipzRYomie8hgh7M However, it won't be an issue in a file like this. It's only going to make a difference if you're copying the formula down thousands of rows and/or you have a lot of other complex calculations in the model... Thanks again and have a nice weekend! 🙂

[@wayneedmondson1065]

@@ExcelCampus Thanks Jon.. great advice on CHOOSE and performance :)) Looking forward to your next post! Thumbs up!!

[@bobmilani6109]

Dude ... you saved me from myself! Well done!

[@ExcelCampus]

Glad I could help 😊

[@harishgupta5888]

Hi John, Great Video, Thanks for sharing, Just to Add , An additional advantage with XLOOKUP over VLOOKUP for Approximate match is that we do not need to have the data or Table Array in Ascending order, ie, Data can be in any order, Still XLOOKUP can perform the closest match.,

[@ExcelCampus]

Thanks Harish! 🙂 Yep, I explain that advantage of XLOOKUP at about the 8:10 mark in the video.

[@rishabhkesharwani7761]

Sir, As an Excel is a Magic, you are the "Magician" of Excel 🤠👍

[@ExcelCampus]

Thanks so much Rishabh! 🙏

[@rishabhkesharwani7761]

@@ExcelCampus 🇮🇳💕u Sir

[@lifeisgoodlg]

Thanks it's great teaching

[@Jay_Beez]

Very well explained. Thank you!

[@ExcelCampus]

Thank you Jay! 🙂

[@harumih.3727]

What is the ground, or the reason that VLOOKUP/XLOOKUP has to use "Tier Minimum" instead of "Tier Maximum"? Is it a rule for VLOOKUP or XLOOKUP formula?

[@darrylmorgan]

Great Tutorial! XLOOKUP Is Awesome And So Easy To Use..Thank You Jon :)

[@ExcelCampus]

Thanks Darryl! 🙌

[@nyamdulamnyamjav9700]

Finally found what i need here

[@davidpowell9644]

Awesome Jon! Thank you!

[@sachinrai9062]

You are great Thank you

[@haydemorales8306]

In this video, you showed one look-up value, but I have ~ 800 product codes (look-up value) that I need to compare to a table to assign specific values. Is there a video that shows a more complex example?

[@babupalek]

Thank you Sir 👍

[@ExcelCampus]

Thanks Babu! 🙂

[@jpfluellen4180]

MR Excell wizard. How can I calculate a commission for real estate Example 3% average commission of gross home sales 85/15 split 12K Cap (Cap comes from the 15% of the 85/15) once the cap is reached its a 285 flat fee per transaction.

[@dirkstaszak4838]

Hi Jon very nice, but what about the ifs function. Even though v or xlookup are more flexible, the ifs function is better than the nested if you suggested or is there a reason why it was not mentioned?

[@ExcelCampus]

Hi Dirk, Great point! I forgot to mention IFS. This would require the same number of logical tests, but make the IF formula a bit shorter. I'll do a followup video on IFS. Thanks again and have a nice weekend! 🙂

[@RonLeedy]

I've been converting long nested If to VLOOKUPs and found out the number of dinosaurs in my company who think its evil black magic.

[@Rkeev1]

Why not simply use Lookup?

[@ExcelCampus]

Great suggestion! There are so many ways to solve for this. I'll do a followup video with alternative methods. Thanks again and have a nice weekend! 🙂

[@tommyharris5817]

VLOOKUP is still better and simpler than XLOOKUP