No video :(

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

Подписаться 575 тыс.

Просмотров 40 тыс.

50% 1

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video, I compare the IF, VLOOKUP, and XLOOKUP functions for calculating commission & tax rates in Excel. Learn which methods are the easiest to write and maintain.
I also explain what a nested IF formula is and how they work.
These formulas will work for any type of rate table where you want to lookup a number within a range of numbers and return a result from an adjacent column. The logic for each tier in the table is: if a number is greater than or equal to a value and less than or equal to a larger value, then return a specific result.
The return value can be a number, percentage, or even text.
Download the example file: www.excelcampus.com/functions...
Related videos & posts:
VLOOKUP Tutorial: Everything you need to know: • Excel Vlookup Tutorial...
XLOOKUP Pros & Cons: • New Xlookup Function: ...
XLOOKUP Explained in 3 Minutes: • Xlookup In Excel: Expl...
IF Function Explained: www.excelcampus.com/functions...
00:00 Introduction
02:15 VLOOKUP Formula
08:35 Formula Maintenance

Опубликовано:

4 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 33

@agnieszkajedrzejewska-wnuk641 4 года назад

Very good video with a good example and useful explanation!

@bobmilani6109 3 года назад

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

@ExcelCampus 3 года назад

Glad I could help 😊

@nyamdulamnyamjav9700 3 года назад

Finally found what i need here

@rishabhkesharwani7761 4 года назад

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

@ExcelCampus 4 года назад

Thanks so much Rishabh! 🙏

@rishabhkesharwani7761 4 года назад

@@ExcelCampus 🇮🇳💕u Sir

@matteozurli269 4 года назад

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 4 года назад

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

@davidpowell9644 4 года назад

Awesome Jon! Thank you!

@wayneedmondson1065 4 года назад

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 4 года назад

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). ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Tkn1VlFc-QQ.html 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 4 года назад

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

@darrylmorgan 4 года назад

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

@ExcelCampus 4 года назад

Thanks Darryl! 🙌

@lifeisgoodlg 4 года назад

Thanks it's great teaching

@Jay_Beez 4 года назад

Very well explained. Thank you!

@ExcelCampus 4 года назад

Thank you Jay! 🙂

@sachinrai9062 3 года назад

You are great Thank you

@harishgupta5888 4 года назад

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 4 года назад

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

@harumih.3727 3 месяца назад

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?

@babupalek 4 года назад

Thank you Sir 👍

@ExcelCampus 4 года назад

Thanks Babu! 🙂

@haydemorales8306 4 месяца назад

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?

@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 4 года назад

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 4 года назад

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 4 года назад

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 4 года назад

Why not simply use Lookup?

@ExcelCampus 4 года назад

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! 🙂