Original subtitles

Now I've shown you how you can combine the index and match functions to perform a powerful, complex

look up.

We're going to see how we can do exactly the same thing in a different way.

When I say in a different way, I mean using two brand new functions in Excel 2021.

And that is X lookup and X match.

Now, as I said, these two functions are brand new to excel 2021, and X lookup is really another way

of performing a look up very similar to index and match.

So it has all of the same flexibility that we love about index and match.

But it's a lot easier to construct.

So let's use the same examples.

But this time, instead of index a match, we're going to use X lookup.

So let's click in.

I'm going to tie pin equals x look up.

Now, the first difference you'll notice here is that we do have a lot more arguments and it's only

the first three that are mandatory.

All of the others, the last three are all optional arguments.

Now we're going to go through all of them just so you can see exactly what they do.

But if you just wants to do a basic lookup, you could just use the first three arguments.

So the first argument is look up value.

So I look up value is going to be whatever we have in cell H5 comma, what is our lookup array?

So where are we going to find Google Docs?

Well, we're going to find it in the apps list.

So this is our lookup array and then we simply need to specify the return array.

So what do we want to return?

We want to return the category, so all we need to do is select the return array.

That is basically it.

I could close off my formula, enter and I'm going to get my answer.

Now, how much easier is that than doing the same thing using index a match?

Now let's double click to go back into this formula because I did say I wanted to run through all of

these arguments.

So the next argument is, if not found, so we have some additional things we can specify here.

So if the lookup value is not found in the table, we can choose to return a value that we specify.

So that might be a piece of text.

Remember, text needs to go in quote marks.

So I'm I just want to say here, not found.

Comma, I can choose what type of match I'm doing.

Do I want to do an exact match?

Do I want to do an exact match or next smaller item on next large item?

Well, I'm going to do an exact match.

I want it to exactly match Google Docs.

And then the final optional argument that we have here is the search mode.

So with this particular argument, we can choose which way or how we're searching through this table

so I could choose to search from first to last or last to first.

Now, if I do first class, that's type one as our final argument, it's going to return the result

of productivity.

Now we're going to do X lookup again to return the profit and the revenue, but we're going to do this

in a slightly different way again.

Now, so far, we've seen how we can create named ranges and use those in our formulas.

But what about if I want to put this data in an Excel table?

Well, what I'm going to do is click in my data and press control.

T yes, my table has headers.

Let's click on.

OK, now I don't particularly like this formatting, so let's change that.

That looks a little bit better, and I'm going to give my table a name.

So let's call this app's underscore list and enter.

So now what I could do when I'm constructing this X lookup, let's do it again.

So x look up and look up.

Value is what we have in Cell H5.

My lookup array is a table this time.

Now I've called my table apps list, so I'm just going to start to Typekit double click to select that

table.

Now it's selected.

The entire table for my lookup array is just the app column.

So what I can do here is press the square bracket, and it's going to show me all of the different columns

of that table so I can then go through and just select the app column.

Press the tab key to select it and close that bracket.

So now I've isolated that column comma.

What is my return array?

Well, my return array.

This time I want to return the profit.

So again, I could use my table columns.

Let's choose the table.

Square bracket.

And I want to choose the profit column this time.

Typekit to select and close that square bracket.

Now if I want to essentially skip over an argument so I don't particularly want to have any, if not

found text, I can just press two commas to move on to the next argument.

I want to do an exact match and I want to search first to last.

Close the bracket.

Enter and it's going to return my result.

Let's double check to make sure that this is correct.

So let's find Google Docs and I can see that yes, the profit is one one six six.

Now for our final look up just here, I want to focus in on that search mode.

So which way we're searching through our data?

So let's do our look up.

One more time I look up value is H5.

I look up a ray.

Well, I'm going to use my table range again.

I'm looking up the app in the app column.

The return array this time is going to be the revenue, so let's select our table and the column.

I'm not going to put in any, if not found tax, let's press another comma to go on to the next argument.

I want to do an exact match and I'm going to search first to last.

Close the bracket.

Hansa and I get my results.

Now with that search mode searching first to last.

That works perfectly fine for the data that I'm using.

But what if I have Google Docs listed twice in this table?

So what I'm going to do is on the bottom here.

I'm just going to add another row for Google Docs, and I'm going to assign it to a different category.

So let's say utility, it's going to be Google Docs again, and let's just give it a value of a 50000

with a profit of, let's say, five thousand.

Now I'm just going to expand my table out to accommodate that new data.

So now effectively, I have Google Docs listed twice in this apps list.

Now, currently, if we use the revenue as an example, it's show me the revenue of the first Google

Docs that it finds in this list because I chose to search from first to last.

But if we double click to edit this formula, what I could do is I could change this to something different.

So let's do search last a first minus one and her answer, and we get a completely different result.

This time it's searching from the bottom upwards, and the first Google Docs entry comes to is this

one just here?

So it's returning a result of 50000 for the revenue.

So that's how that search mode option works.

But as I said, in its most basic form, if you don't want to use all of those optional arguments,

you just have three arguments look up value lookup array and return array.

Super simple.

So now we know what X lookup does.

What does X match do?

Well, X Match is fairly similar in many ways.

We have a lot more arguments than we do when we're using just the basic match.

So what I could do up here is if I want to go back to using index a match, I could do an index with

an X match instead.

Our array is the category.

So let's select it and notice that because I have this data and a table is automatically going to put

the table name and the column name in for me, row number.

Now, if you recall, previously we were using the match function to automate the finding of that row

number.

But what I could do is use the new X match function instead.

Again, the last two arguments here are optional arguments.

Now I look up, value is what we have in H five.

My lookup array is where I'm going to find that.

So that's in this apps list.

I can then choose a match mode, so I want to do an exact match.

And then I also get that search mode option when using X Match as well.

So I can choose if I want to search from first to last, last, a first or start doing a binary search

and sorting in ascending order.

So let's do laster first again, minus one.

Close the bracket.

Enter.

I can see that I've forgotten to add an additional bracket on the end, so I'm going to accept the correction

and it pulls back the category of utility because it's finding this entry fast because it's searching

lost too fast.

Now you can also use X match on its own when not combined with index, and it works very similar to

the match function on its own.

It's basically going to tell you what the row number is.

So the arguments are pretty similar.

Look at value lookup array as where we're going to find that which is just here.

My match mode is exact, and I'm going to search let's do first class this time.

Close the bracket, hit enter and it's going to tell me what row Google Docs resides in from the top.

So pretty much the same results that you get with Match, but it's just a little bit more flexible.

You have a few more options in there.

So that is your introduction to two brand new functions in Excel 2021.