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