Why XLOOKUP Reigns Supreme Over SWITCH in Excel

hi I'm Jamie and welcome to teachers Tech I  recently did a video about how you could use   the switch function in Microsoft Excel to replace  the IF function now I want to go a step further   and show you how you should be using XLOOKUP instead of the switch function it's far superior   so I'm going to redo the examples that I did in  the switch function video and show you how it can   be better done with using XLOOKUP in Microsoft  Excel if you'd like to follow along with today's   tutorial I'll put a link to this practice sheet  Down Below in the description so you can click   on it download it and create these formulas  as we go along here's the first example that

I showed using the switch formula and if you want  to check out that video I'll put a link up above   in the card and down below in the description you  can see the switch is still here and I'm going to   replace this one uh the switch very easy to use a  little bit cleaner than the ifs function because I   don't have to make the reference to the cell each  time I can just go ahead and write it like so but   using X lookup is prob probably the approach you  should be using with a separate range or table   so I could have this on a different sheet if I go  ahead and start my X lookup uh function right here   I'm going to go and still reference the same cell  that I did before now I just have to go and have   my different uh ranges chosen through here so I'm  going to pick my first one now my second one here   uh if I was copying it down I'm just going to go  ahead and absolute those uh ones right there and   I can also say to return if the value is not  there I could return a different value but in   this case I don't need it so right now I'm going  to copy this down and you'll see that I get all   the same results so here was my switch formula  here and here was my X lookup now if I want to   make any changes I can quickly instead of going  inside the formula here and making any changes I   can just go down here write a different word and  it will quickly update dynamically into wherever   I need it so definitely advantage over X lookup  over switch here is my example number two with

the switch formula I just multiplied it by total  sales and I put my commissions right in here after   it identified the region now I'm going to redo  this with X lookup I have my separate information   over here I'm going to go and start the same way  I'm still going to multiply this right here by my   X look so I'm going to go ahead and multiply and  start my X lookup at this point so when I choose   this I'm referencing this one cell also and then  I can choose my first lookup array it's going to   look up this one right through here and then the  second one is going to be this one at this point   I'm just going to close it and I'm going to make  sure at this time I'm going to put my appsolute   around all of these just like that and let's  copy this down I'll just drag this down all   the way and you can see it gave me the exact same  uh information and I find that actually a little   easier and again making any changes to this  if the commission rates change it's just more   efficient this way to make any updates now if I  was going to go a step further let's say if this   was deleted this Central one right here notice  that the error that I get here the nice thing   with using the X lookup now if I go back up to  this one here I'm going to add one more thing so   if I go here let's say if it's nothing there I'm  going to go if not found I want it to be zero so   at this point if I copy them all the way down now  that gets rid of the error because even though I   don't have it written down into my region it's  just if not found it returned that zero there   so another region another reason why I like having  the X look up as my first choice over switch here

here was my third example of how to use the switch  function in Microsoft Excel uh it was right here   and you can see I actually added an if and is  blank functions as well so what it's going to   do with the is blank if this was blank it's going  to return a zero or if this was blank it's going   to return a zero and I'm actually going to add  this to the X lookup um at the end of this too   so let's go ahead and create our X lookup in this  cell so I'm going to start with my equals we're   actually going to add two x lookups together  so in this case my cell I'm going to look up   is this one right here and the first array will  be the difficulty level here now I'm going to go   to my second array this one right here and I'm  going to make sure that I'm just going to go and   uh use my function F4 to Absolute those now I'm  going to close this I could have an if not found   me zero but I'm going to use the is blanks anyway  so I'm going to just leave that as it is there so   I can put my addition in and start my X look  up again and this time it's going to look up this   cell and we're going to look over here at the  array here and we're going to look at the array   here and again I'm going to add my uh my dollar  signs to all of these with the simple F4 here so   I think that will do this if I go ahead and close  it just like this I get my five across does it   copy down here yes it does everything is exactly  the same so remember I wanted the is blank so if   this was blank uh if this case was blank right  here notice there's an error here so I'm going   to go back and actually do the same thing that  I did with the switch one I'm going to take this   formula right here and I'm going to go ahead and  just add the if rate at the front and you can see   there's the if or is blank part and then it just  has the rest of it just like I wrote it before and   I did it with one more bracket at the end so now  if I copy this down here if one of these happens   to be blank like this notice it just becomes  a zero so I hope this video has shown you why   the X lookup function will reain over the switch  function uh switch does have its place but you   really can't compare it to how powerful uh the X  lookup is take a look at my other video about the   how to use x lookup and I go through it step by  step uh giving different examples as well thanks   for watching this time on teacher Tech I'll see  you next time with more Tech tips and tutorials