馃攳
Excel Two-Way XLOOKUP - How to use XLOOKUP with two criteria in Excel | Nested XLOOKUP Tutorial - YouTube
Channel: Chris Menard
[0]
Hello, I'm Chris Menard.
[2]
In today's video, we're going to do a two-way
lookup with Microsoft's awesome XLOOKUP function.
[15]
So before I do this, this is going to be a
really short video.
[19]
XLOOKUP came out in 2019.
[21]
I've got a full blown video on all the features
that I'm about to list.
[26]
Before I show you the two-way lookup with
XLOOKUP, five reasons that I love XLOOKUP.
[32]
Reason number one: unlike a VLOOKUP, which
will only look to the right an XLOOKUP will
[38]
look left and right.
[40]
Reason number two, an XLOOKUP can return results
based on multiple criteria, which I'm about
[47]
to demonstrate.
[49]
Reason number three, that I love in XLOOKUP
the default for XLOOKUP is exact match.
[57]
Unlike a VLOOKUP, which is not an exact match,
you have to make it.
[61]
Reason number four, an XLOOKUP will not just
look vertically like a VLOOKUP, it'll look
[68]
horizontal like an HLOOKUP, because there's
an HLOOKUP and a VLOOKUP, XLOOKUP does them
[74]
both.
[75]
And finally, in XLOOKUP, we'll actually look
from last to first, a VLOOKUP will not do
[82]
that.
[83]
So let's jump into Microsoft Excel right now
and do this two-way look up.
[89]
So I've got data in A1 is blank, but I've
got quarter one to quarter four up at the
[94]
top.
[95]
I've got three employees listed.
[96]
I just put in three employees to keep this
easy.
[98]
And then I've got these numbers right here.
[101]
Here's my criteria.
[103]
I'm looking for, let's put me in there, Chris.
[107]
And I'll keep this really simple, I'm looking
for quarter one.
[109]
So I should get the number 17 here.
[113]
XLOOKUP.
[114]
I'm going to look for Chris comma.
[118]
Notice it says look up array or where's Chris.
[122]
Right there.
[125]
Comma.
[126]
What is it you want to find?
[128]
Well, this is where I'm going to do another
XLOOKUP, so this is a nested XLOOKUP right
[133]
here.
[134]
Well, I still want to look for Q1, comma.
[139]
Where is that?
[142]
Comma.
[144]
And if you find it, what do you want to return?
[147]
By the way, another reason I love XLOOKUP
as a trainer, it is so much easier to explain
[153]
than a VLOOKUP.
[156]
So there you go, right there.
[157]
There is my formula.
[160]
I hope I get the number 17.
[163]
Click yes.
[165]
And I do.
[166]
Let's test it.
[167]
Let's look at Q2.
[168]
It is not case sensitive.
[170]
I get the number eight.
[172]
Change Chris to Mary.
[179]
Helps if I can type.
[181]
And I got 20.
[183]
Last one.
[184]
Let's change Mary to Q4 and I should get 20
again.
[187]
How about Q3?
[189]
15.
[190]
And finally, oh, and it's 10 for Q3.
[194]
There you go.
[196]
So there's your XLOOKUP looking two ways which
you could do with an index and match function.
[202]
Again, I just find it easier with XLOOKUP.
[206]
Let me know if you have any comments or questions,
feel free to subscribe.
[210]
My full-blown XLOOKUP videos will be down
below.
[215]
Thank you for your time.
[217]
Have a great day!
Most Recent Videos:
You can go back to the homepage right here: Homepage