Transcripts
1. Lookup functions intro: One of the most used functions in Excel or lookup functions, which led to extract
data and match data. You have different
variations like VLookup, HLookup, x lookup,
an index match. If you want to get
up and running quickly with these functions and also see more advanced use cases than this training is for you. Hi everyone. I'm biased. I'm a trainer and consultant for axial Power BI and Tableau, run my own company
data training. And I'm also a YouTuber. I've built this complete
online actual training to help you master axle and the quickest
way without wasting times to learn things that
you want to use in practice. There's trainings by five of the actual boot
camp where you will learn about everything around the different lookup functions.
2. VLOOKUP | The basics: In the previous section,
we've talked about all of the fundamentals about working
with functions in Excel. Now it's time to start exploring different
types of functions. One very important category
is the lookup functions. Now here we have different
types of lookup functions. We have fee lookup,
HLookup, we have AX lookup, we have index match,
and we're going to talk about all of
that in this section. So let's get started and open up the workbook, everything
about look-ups. Now, first of all, what I lookup functions and why
do you want to use them? Not to show you that. We're going to go to
the very first sheet, 01 lookups where we have
a very small dataset about different beverages
that we can order in a bar. Alright, well, let's
say we want to know the price of a
medium-sized green tea. And I want to do this without using any formulas or functions. How would you approach this? But what I would do, I would
go to their beverage column, go down the list until I
find green tea over there. And then I go to
cells to the right, because there we have
the medium-sized prices. And this way I get
to the price to 45. Now because this is a
very small dataset, you could do this, of course
very easily in a manual way, but just imagine would have hundreds of thousands of rows, then this would take
quite a bit of time. And what if you want to know the price now for an espresso? Well, you would have to go
down the list again and again, manually look for
it, and then say which size you want
and extract the price. Now, instead of
doing it manually, we can of course, do this
with lookup function. And the most common lookup
function is a VLookup. Let's start with the
VLookup function. Over here. We're going to look
up the price for, let's say that green tea. And the size that we're
going to look up is medium. And we want to look
up the price, okay? Now, here for the price, we're going to write a VLookup. So I type an equals sign VL and the VLookup
function pops up. That looks for a value in the
leftmost column of a table. And that's important,
the leftmost column of a table, okay? And then returns a value in the same row from a column
that you specified. And by default, the table must be sorted in
ascending order. Let's go over this step-by-step. Here. I'm going to select
it by pressing Tab. So I do not write
the full function and bracket open,
I just pressed up. Or if you prefer, with the market can also
just click on it. Now, we have four
different arguments that the first one
is the lookup value. We want to look up green tea. Now, we could do
this by hard-coding it like this, green tea. And because it's tax, we
need the quotation marks. Or alternatively, we can also refer to the cell that
contains the lookup value, which in our case
is a 60 green tea. Alright, comma to go
to the next argument. Now here we need an table. Array doesn't necessarily
have to be a table. It can also be just
a dataset, right? So over here we
have our dataset. It's not a table or it's
not formatted as a table. Okay? And what is important is that we start with the Lookup color. So we do not start
over here in column a. We're going to start in
column B and we select everything all the
way till the end. Now, the colon from which
we extract the information also needs to be in this table
array inside of the range. So if we only are interested
in the medium prices, well, then I don't necessarily need to select the very last
column, however, to be a little bit more
flexible later on, I also include the very
last column as well. Okay? Now before I do anything else, I'm going to enter a comma
to go to the third argument, which is the column
index number, not the column index number from which we want to extract
the information. So that means with the information that
we currently have, It will look up green tea in the very first
column of the range, finds it over here. And then we have to say, from which column do you want
to extract the information? Well, if we want to know the price for a
medium-size green tea, well then we have to go
to column number one too. Three. You start counting from the most left column in the
range that you specify. So not from column a. Now you start counting
here from column B. Column B is one, C is two, and d, that is free. So that's why we dive in F3, F4. The very last argument
we have to say, do we want to have
an exact match or do we want to have
an approximate match? Now, most of the times you probably want to
have an exact match. Now, we will get back
to this a little bit later to explain exactly
what the difference is. But if you're not sure, let's go for an
exact match. Okay. Now let's close the
brackets, press Enter, and it gives us 245, which is indeed over here
the price of a green tea. Perfect, Okay, and now what
if we're interested in the price of a medium-sized
espresso, Well, then we just have to
change the value here in the name of the
beverage to espresso. Press Enter. And now we have 375, which is a medium-size espresso. And what if I have
a lowercase e? Does it still find it? Yeah, steelworks. So it's not case-sensitive. And what if I don't want to
have a medium-size espresso, but maybe just a small one. Well then we just go back
over here to the formula. And instead of a
three-foot column index from which we want to
extract information, and we'd have to change it to E2 because the small prices. Are in column number two of the range that
we specify, okay? And then we have 315, which is indeed the price
of a small nespresso. Now let's go over the steps one more time on the next sheet. So let's go here
to Z11 exercise. And here we have a
different type of dataset with financial information
for different accounts. Now, I want to extract information for one
specific account, and that is the account 6,805. So if we would do
this in a manual way, we would take the number, go to that column with
the account numbers, and then you would go
down the list until you find 6,805, which is over here. And let's say we want to
know that value for debit. Then we would go to
the god right next to it and then return 2132. Now, all of that I want to do, we have a VLookup function. So we go here to D5 and start typing our VLookup
function again to select it, press Tab at the lookup value
is over here in D4 comma, then the table array. So just the range. Well, this range needs to
start with the Lookup color, okay, Now, that
is very important and a lot of people
often go wrong. So we are not starting a
range here and got enough. We're starting and range with
the account number column. Okay? Now we want to return the value in
the debit column. So we at least need to
go until that column. However, if we want a little
bit more flexibility to be able to also return
values in the credit column, then we have to include
the golf game as well. Okay? So with that selected, I press comma to go to
the third argument. Now what is the
column index number? Well, that is the colon from which we want to
extract the information counting from the
most left column inside of that range
that we just specified. So the debit column is in
column number 12 of that range. So we type in it too. Then. Do we want an approximate
match or an exact match? We wanted to have
an exact match. So we type in false. Never leave this
last argument out. It is optional, but
if you don't specify, it doesn't approximate match and could give you
the wrong results. So watch out, always
type in false. Data, gives us 2132. Let's see if that is correct. Well, over here we have 6,805 and we have the
debit amount of 2132. Perfect, So a VLookup,
it's working. Now. It's also very
important that you know, when to use a v lookup and sometimes people go wrong there. So I have another example on the next sheet, 01 duplicates. Now here we have a
different dataset, also with different accounts, different years, account
description and amount. Now what I want to
do is look up again the account number and here
the very first column. And once I find that, so 600857 is over here, then I would like to return
the corresponding amount, in this case, 7,730. Alright, now, you might think, okay, we can do this
with a VLookup. Yes, we can now write it
as equal to V lookup. That's the function I need. I want to look up the
information and D4, I'm going to look
it up over here, starting with the very first
column, Control Shift down, Control shift to the right to
select them, Diana dataset. And before I go up, I enter a
comma so that when I go up, I do not change the
selected range. Okay, so over here for
the column index number, I have to press backspace. And then for the
column index number, and we can type in four
because we want to extract the value in
column number 1234, okay? And we want to have
an exact match. So type in false. Close your brackets. Under 7,730, exactly the amount that
we weren't expecting. However, if you
have a closer look, you'll see that we
have that same account multiple times in this dataset. We have it over here. And if we go down a
little bit further, you see we have it
over here as well. Then if I go down again
a little bit further, we have it here as well. You see, we have it three
times because we have information data
for multiple years for the same accounts. Now what happens is
that it only returns the very first entry for that account number,
not the other values. And what you might be hoping
for is that you would get sum of all three
of these values, the value for 2019202021. However, then we are using the wrong function because how could we get to that number? Well, we have
already seen that in the previous section
because then you would want to do a sum F. So you
would like to then sum all of these amounts if
the account number is 6,857, so you will not use it V lookup. So instead of a
VLookup function, we could write as sum F are some Fs function that the sum range that is
done the amount column. And then we have the range which would be
the gum number colon, and then the grid itself, which would be the
account number that we fill out an d four. Alright. And now it gives me 18,941, which is the sum of this one, that one, and that one. Alright? So here you would
not use the VLookup because we do not just want to extract the information of one, we just want to sum the amount
for a specific account. So VLookup, you don't use
when you want to sum, I think the average count off at different values for that
corresponding lookup value. Here, that would
be the gum number. Instead of that, you would use the conditional functions like SUMIFS, AVERAGEIFS,
and countless. Okay, So now you know
the basics of VLookup, but there's also HLookup and that's what we're going to
explore in the next part.
3. HLOOKUP | Same logic, just the other way around: We have just seen how to
use the VLookup function, which is probably the most use lookup function that's
available in Acts. However, there's also HLookup, which just means
horizontal lookup instead of v, vertical lookup. So let's see how it works. And for that, we go to
the sheet 0 to HLookup. Now here we have a small
dataset where we want to look up the values
for different metrics, like cost-per-click,
number of clickers, number of bookings
for a certain month. Now the month we
have at the top, and I want to type in certain month and look it
up in that very first row. And once I find it, I want to go a few rows down to then return the value
for that metric. For example, cost-per-click
for October. That would mean, I
look up October over here in the very first
row, find it over there. And then if I want to go
to the cost-per-click, then I just go one row down, and that is then
the value that I return for October,
cost-per-click. Now let's do this
then with HLookup because that is exactly
how that function works. Now, we go over here to the cell E6 and type in equals sign. And then H L, H lookup function pops up, selected by pressing Tab. And then what is the lookup
value while the lookup value we have over there,
October, now, Goma. Where do I want to look it up? In what range? Now? Here, instead of the
very first column of the range that we specify, it's going to look up
the lookup value in the very first row of the
range that we specify. Okay, so if we select
the whole dataset here, including the headers,
then it's going to look up October in
adults over here. Okay? Now, another comma. Once it finds the lookup value, to which row do you then want to go to extract the information? Now, we want to have
here the cost-per-click. So counting from
the very first row, we want to have the
information in row number two. Then the last argument also
here, always fill it out. We want to have an exact match. Close the brackets, press
Enter, and there you go. We have $0.48. And what if we also want to get the number of clickers
revenue from accounts? Well, we would like to just take that formula and drag it
down to the other two, and it gives us an error. Now, why is there an error? Let's have a closer look. I'm going to go over here to seven and then clicking
the formula bar AC, that range that we specify. Well, it's shifted down as well, and the lookup value is
not October anymore, but the sound below it. So if we just want to simply drag a copy or a formula
to a different cell, then we have to make sure
that we fix our references. Now, how to do that? Let's go back to the original
formula where you started. Now I want to fix at least
the row number here for e4, because when I drag it down, I don't want this
reference to October to go to the next cell below it. Okay, so then the range that we specified here for
the second argument, well, that shouldn't move it up. So I'm going to use F4 to lock of your d 11
and to lock B6. And then the third argument, while we still want to
have here, cost-per-click. So I'm not going to
change that and present. Now we can drag it down. I see, well we have the
same value everywhere. However, for the
number of clicks, we can go back and change the two to three to get the
next row in the dataset. And here for the
revenue from clickers, which is the very last one, we go to row six. So I have to change
the two into a six. Okay, so our age
lookup is working, however, let's practice it
again on different datasets. So we go here to 0 to
exercise to where we have that same dataset as we had at the beginning for
the VLookup function. But now we're going to
do an HLookup here. Now, let's say that the
beverage for which we want to extract the price is going
to be again, the green tea. And then the size that we
had before was medium. And we want to
extract the price. So how could we do
that now with HLookup? And how is it different from that VLookup that we had before? Now, I'm going to write
an H look-ups it. I'll select it by pressing Tab. Then the lookup
value that we have. Well, here we are going
to look up horizontally. So now we're not looking
at the beverage. No, instead we're going
to look up the size. So the emphasis is now on the right side is not so
much in the beverage. So be 16 is the lookup value. Now, if you like,
you can fix it. It's not that we're going to
copy it over somewhere else, but let's press F4 to fix and reference Goma than the range, the range where we
want to look it up. Well, it needs to start with the very first row because that's going to be
the lookup row. Okay, So here, whether or not you include the beverage column doesn't really matter. The columns that you
really need are these ones over here and then
the row index number. Now, where is the beverage
green tea, on which role? While green tea is over here. So that is row number 11. I'm going to type in 11. Then do we want to
have an exact match? Yes, we do. So diving false for the very last argument
and then Enter 245, which is indeed the right price. But you see over there,
what if we want to find not the medium-size but
the large sites price. Well, then I just
go here to size and change medium, medium to large. And now we have 265. And what if we want to have the price for
different beverage, for example, gold brew? Well, nothing changes when I changed the beverage
neck because here we have no reference to the cell where we type in
the name of the beverage. So if you want to look up the price for different beverage, I would have to go back
to the formula and change the 11 here to, well, let's say I want to
look up cold brew, which is a row number nine, then I have to change
that to a nine. Then we have 325. Nice that correct. Not cold brew is over
here, is large size. So indeed is correct. Let me get rid of this
color and that's it. Our age lookup works. Now you see the difference
with the VLookup from before where we did a vertical lookup in
the beverage column. Now with the HLookup, we're looking up the size
horizontally in the header row. Then once we find the size, we go to a certain row. So the row is a little
bit more fixed here. So now we have seen and practice a little bit with
VLookup age lookup. There's also acts lookup, which I'm going to show
you in the next part.
4. XLOOKUP | New and improved version: The newest of all of the
lookup functions is x lookup. And actually, if
you're not x look up, you don't really need to
know VLookup, HLookup. So let's see it in
action. Not for this. We're going to return
to the very first sheet in the workbook 01 look-ups. Now, over here we
were looking up the price for espresso
medium-sized. Okay, now I'm going to delete
what ever you wrote here in C6 and now write an x lookup. Now let's see how
it's different. I'm going to type
an equals sign. Now we type in Excel, not an x lookup function absorb. Here we have the description of x lookup and is almost the same. However, there's a
subtle difference. Searches a range or
an array for a match and returns the corresponding
item from a second rage. So here we have to
set up two ranges. One rage in which you're
going to do the lookup, another range from
which you want to extract the information. Okay, now let's select it and seeing how
it works in action. Lookup value, that
OVN A16 comma, then the lookup array. Well that's going to
be the lookup range, so the beverage column, and it doesn't
really matter if you include the header row or not. Gama. And the big
difference is that we didn't include the
price columns. And before we did include it comes from which we want to
extract the information. However, with the ax lookup, you just specify the extract
range in the third argument. So if we want to have the price of a
medium-size Espresso, then we're going to
select column D there. And the number of
cells that we have in that second range
needs to correspond to the number of cells
that we selected there for the first range, the lookup branch. Okay? Now these three arguments you need to specify at a minimum. And then there are bunch
of optional arguments, which we'll have a look
at in a second, okay? Now, by default it always
does an exact match. You don't need to
bother with that. Over here, I see we
have a price of 375, which is indeed the price
of a medium-sized espresso. But now we extracted
information using an HLOOKUP. Now let's double-check
if it works. Let's change over
here to London. And you see we have here for 25. And what if I write
it lowercase, lowercase i, then
it still works. So also here, it's
not case sensitive. Okay, so let's go over
these steps one more time for 01 exercise. Here we wanted to
extract the amount in the debit column for a
certain account number. This time we're going to
write it using an ax look up. What is the value
we want to look up? That is the account number
and d for the lookup array. So the lookup range, that is going to be
the account number. Now, before we didn't
include that as pitch. If you want, you can
include that as well. Then a comma, what
is the return array? Well, we want to return
the debit amount, so that is the range
right next to it. And because I included
added before, I have to include it over here. Now done, we can close the
brackets, press Enter. I see 2132, which is indeed over here the corresponding
amount for accounts 6,805. Now one of the big benefits of Acts lookup is that
for a lot of people, it feels a little
bit more intuitive. And you can also do
a horizontal lookup. So it replaces both VLookup
as well as HLookup. So if we go here to 0 to h lookup and get rid of all of these formulas that we
wrote there before. And we can now use
an X lookup instead. Now, I'm gonna go
here to Sx equals sine x alpha x lookup,
not the lookup value. That is October, the month here. So then where do we
want to look it up? Now we're going to
have a horizontal h. Now that horizontal
ranges over here. So all of the months, Gama, what does it return
area that for the very first metric,
cost-per-click, that is, well, row number
two, row over here. And we select as many cells as we have for the
very first range. And then we can simply press Enter or close the
brackets and press Enter. And there we have the $0.48, which is the value for October
every change the month, let's say from October to July. Then you see we have 60 fourths. And also with the ax lookup, if we want to copy our
formula to the right or down, we need to fix our references. So the lookup value
for that reference, well, we need dollar
signs, right? So at least the dollar
sign in front of the row number for
the lookup array, we're also needs to be fixed. So I'm going to put dollar signs here and here for
the return array. Well, we could fix
it and we could not. If we don't fix it,
then it will go down. Meaning it takes the next row, which is the number of clickers, which is okay for
the second metric. And if the next one would
be number of bookings, well then I would not fix the reference to
the return array. However, we want to have here, the revenue from Click out. That means that if I
would drag it down here, you see we would have number of bookings instead of
revenue from clickers. So I would need to go back and make an adjustment and say here, I don't want to have row 14, But I wouldn't like to
have row 1616 over there. And now we have 2829, which is indeed the value
for July revenue from Click. Okay, now you might
be wondering, why did you show us VLookup and HLookup if there's acts lookup, which is basically
a better version and replaces both of them. And I agree with you. However, there are a
lot of people that don't know x look up
and work with VLookup. And that's why you still need
to know VLookup as well. However, if you need to extract the information
from the dataset, just use x lookup. It's much more flexible
and replaces the audit.
5. Matching data with lookup functions: Look at functions
are not only used to extract information
from the dataset, there are also
often used to match two or more datasets together. So that you have one big
table with all of the data, which is something that you need for normal pivot tables if you want to use the fields
from different datasets. Now, let's see how that
works in practice. Now for that, I have
a separate sheet. So we have over here 03 matched data and here
we have two datasets. The main one, you're
on the left-hand side, where we have information about the revenues
for different models. And we have a second
dataset which contains descriptive
information about these hotels. So here we have the hotel id. Here we have the hotel id
and the names of the hotels. Okay. What I would like to
do is I would like to look up a hotel id in
that second dataset. Once I find it, extract the information right next
to it so that we have the hotel name in the very first dataset
that's on the left. Okay, Now, how would that work? Well, we could use the VLookup. Next Luca, let's first try. So I'm going to write again
an equal sign for VLookup. What is the lookup value
or don't like D right next to it, G5, Gama. Not Where's my range? Right here? So I'm going to select
the whole range. Then another comma, once
I find the hotel id, so the lookup value, then I want to go to that second column in the range that is specified, number two. And then the last argument, I want to have an exact match. So type in false. Now let's close the brackets, press Enter and see if it
works for the very first one. However, as soon as we
copy the formula down, then you see at some
point we get arrows. And that is because, well, the range that we
specified here for a second argument
also move down. So if we want to match the data, we have to go one step back and we have to fix the reference to the lookup range and
the range for which we extract information because
that one shouldn't move. So here we need at least a dollar sign
in front of the five, in front of the 40. Okay. Then we can press Enter. Okay. Why don't we need a
dollar sign in front of G5? Because that reference
needs to move down. Okay, so there we do not use
dollar signs, okay, now, we can just drag this one down or you just double-click in the bottom-right corner and it copies it down for
the whole dataset. Perfect. And you see now it does work. For example here
for hotel ID seven, that is Ramadan limited. So this is how you can
manage data using VLookup. Now of course, this
would also have worked with an ax lookup. Okay, so here let me
delete what we just did and then go back
again to H5, right? X L for x lookup. We want to look up over here, the hotel id right? Next slide, then
the lookup array. Now, where do we
want to look it up? In which range we
want to look it up, or here in the second
dataset, first column comma. Then we don't array from which goals that we
want to return values. That's the second column
right next to it. Because we are going to
copy this formula down. We're going to fix these ranges. So the second argument, the lookup array,
we need to fix. So I use the F4 key
to fix that range. And the same for the
return array, okay, so you use afford to fix
those references, alright, that close the brackets, press Enter, and then copy it
down for the whole column. And there you go. We
have exactly the same as what we had before
with the VLookup, but now just lift acts lookup. We have two variations here. Now what do we make all
of this a little bit easier is if we
would use tables. Now I'm gonna go
again one step back. And I'm going to
create a table from the very first dataset
on the left now where we have basically all
of the revenue data. So I'm going to
select the dataset, go to Insert and choose table. The table has
headers. Click Okay. Now we can change the name
here on the table design. Table design only shows when you have one
cell in the table selected table design all the
way to the left hand side. And we can call
this one revenue. Okay, so here we have
all of the revenue data. Then we go to that
second dataset, create a table from it. So insert table,
data has headers. So let's write again
that last acts lookup. So now we can see
is equal to then x alpha x lookup
selected by pressing Tab lookup value is
right next to it. Now you see because
we are using a table, we have to add sign hotel id just means take the hotel
id from the same row. Now the lookup array, now we want to
look it up here in the hotel ID column from
the second dataset. Now you see we have hotel info, hotel ID, okay, so hotel info
is the name of the table. Hotel id is the
name of the column. Then the return array. Well, we can either
select that all. We don't arrange the
hotel name column or. If you prefer, you can also
just type in the name of the table, square bracket open. Then we see everything
inside of the table, all of the fields we want to have as we don't
go on the hotel name. And then we can close the
square brackets and then closed stacks look up and see it automatically fills down for the entire column. And this makes it
especially easy if your datasets are in
different sheets. Now, let's look at
another example. Now for this practice exercise, we have three sheets,
we have financials. We're here with financial data. So revenue cost for
different countries, different departments,
different dates. Then we have geographical
information on the next sheet, and we have information
about different departments. Okay? Now you see that we can
match the data, for example, for the departments
under the burden of key, because here we have
the pardon key. And if you look in the
financials dataset, we also have the partition
key and therefore geography. You've seen we have over here Country key, geography sheet. We also have Country key. So we can use those unique
identifiers to match the data. Okay? Now, how can we do that? Well, to make it a little
bit easier for ourselves, we're going to work
with tables here. So I'm going to go to financials and then select one cell
inside of a dataset. And we can do Control T or insert and then click
on table has headers. Alright, let's give
it a good name. Let's call this one financials. Then we go to the next one, geography, do the same thing. So select one cell
in your dataset, Control D, press Enter, and then rename it. And this one we can
call Geography. Then the last one, departments.
Let's go there as well. Control T and call it
this one departments. Okay, So now that
we have our tables, we can match the data. For example, let's say that from the geography table
we want to have the full country name instead of having here the
country, getting that. How can we do that? Well, with our lookup functions. So let me make this column
a little bit wider. And here we're going to
have their country name. Now let's dive in
our lookup function. For this example, I'm going
to go for an extra cup. Now, what is the lookup value? What we're going to look
up the country key. So gantry key means it takes the counter
key from the same row. Then we add a comma. Now what is going to
be the lookup array? Now here, I want to look it
up in the geography table. Now we could go there
and just by clicking the orange geography and then selecting over A2 control shift down to select
the whole column. Then comma, go back
to financials. We could do it like this. However, if we are in the sheet, financials are I would
do it is I would just type in the
name of that table, geography, square bracket open. Then we see everything
inside of that table. We would like to have
the control key, select it, and then
square bracket close. Now, the return array, from which column do we
want to return the value? Again, geography,
square bracket open. And we want to have
the country name or the country Dan square
bracket close again. And then we can close. Actually look up, press Enter. And because there's a table, it automatically fills it down. It's perfect. And that's it. We have the country name. And what if we also wanted
to have the region? Well, then we can just add another one and say that
this is the region column. Now we can just take over
you that very first cell, drag it to the
right and let's see what happens now it
gives me an error. Why? Because it takes
department key now, the next one and we have to change that back to counter key. So it's going to be
tricky and then it's going to look it up
in the country column and geography table. Now, we don't want to
have a counter gallon, but the country
key column, sorry, changed country-to-country
k. And we want to have the information
from the reading gotten. That's correct. So that's that Santa and the automatically fills down and we have the corresponding regions. Perfect. What if we need information
from the burden's not, then we can do exactly the same. So I'm going to have here,
let say the department name. Then we can write our x lookup. What do we want to look up? What the department where
do we want to look it up? We're going to look it up in the departments stable,
square bracket open. And then you see everything
inside of that table. Now we want to match it on
the basis of the bargain K, Alright, square bracket close
and then they return array. Then again we refer to the
Barton sterile square bracket open and we want to have
the department name value. So the bargains are you, okay? Then square bracket, close, close the brackets for
x Luca, presenter. And here we have all of the
phone department names. So you see, working with
Damon's makes this a lot easier so that you
don't have to jump back and forth between
the different sheets. So now you have
seen how we can use the different lookup functions not only to extract information, but also to match data from different tables
or different datasets. However, what we didn't
talk about yet is why do we actually need an exact match and what is an
approximate match? Because I think you
might be surprised.
6. Approximate vs exact match: An exact match versus
an approximate match. What does it exactly
mean and why did we choose so far only exact match? Now what a lot of people
think that it does is that it takes the
lookup value and it looks it up in the lookup range and look for an
approximate match. However, this is
not exactly true. Now here I have a couple
of examples in the sheet 0 for approximate that
as a very first example, we're going to look up certain quantity in the
quantity column over here. And once we find
it, we see, okay, what is the corresponding
discount percentage, okay, so the more accustomed
orders, the high-end, the discount
percentage, alright, now let's say the
quantity is a thousand. Now then we can write
V lookup an X logo. Now here, I'm going
to use for now, first VLookup, V lookup lookup
value is here in D4 comma. Then we're going to
look it up over here. So I'm going to select
the whole dataset, not the first column
and lookup column, and then another comma. Now, once we find the quantity, we want to go to the
value right next to it, which is column two. And we want to have
an exact match. So therefore false. Close the bracket
under six per cent, which is true, which is the corresponding discount
percentage for that quantity. But just imagine we would have not thousands, but maybe 750, don't want 750 is not inside
of that first column, so it doesn't find it. However, you would still get a discount if you
order 750 products. Now how much discount? Well, probably the four per
cent that you have here, right next to 500
because you're in the bracket from 502 thousand. And that is where
approximate match comes in. Now if we go back to a formula
and change false to true, now we're going to do
an approximate match. See, now it returns that 4%
over here, right next to 500. Now why does it return
the four per cent? Because when an
approximate match does, it takes the salmon and a 50, if it cannot find it, it goes to the largest value. And the NIF, the lookup
value, which is 500, and then goes to the second column and
extracts the information. Now what is also
really important for this to work is that the values that we have in that lookup column are
sorted in ascending order. If not, then it might return
you the wrong result. And that is why it can
also be so dangerous to not specify that you want to have an exact
match because then by default a VLookup always
does an approximate match. My just return you
a random result, so never leave it out. Okay? But now let's
look first at a few other examples where we can use this approximate match. Now if you go a
little bit lower, we have over here a second dataset with
different tax brackets. Now here I would like
to dive in income. So let's say we have an
income of 50 thousand, then what is the
corresponding tax rate? Now, for this, we can use again a VLookup lookup
value over here. And D 13 comma the range
where we want to look it up. Well, we want to look at it in the very first column of
this range over here. And then we to extract the information
from the third goal. Okay? Now let's start again with exact match. Close the brackets. Now it doesn't return
anything because 50 thousand is not in
that very first column. However, we do know that
we are in the bracket nine thousand seven
hundred forty four and fifty seven thousand,
nine hundred eighteen. So a tax rate should be somewhere in the
range of 40 to 42%. Now, how can we make sure that we return the
corresponding tax rate? Well, this is again where we have approximate
match for it. So we go back and we
changed false to true. Now we have that
value, 14 to 42. Okay? Now, what if we have
fifty seven thousand, fifty seven thousand,
nine hundred seventy? Nothing changes. It doesn't jump to the next one, even though 57,918 is
close and then 9,744. Now, Is that good? Yeah,
It's good because well, we are still in the bracket. Nine thousand seven
hundred forty four fifty seven thousand,
nine hundred eighty. Now allows example where this approximate match could
be very helpful is to, well, again, matched data. For example, if you have certain accounts
here that you want to match to a category. Now, let's say that
for certain category, you have always arrange of
different account numbers. So for example, for a d
it's from 0 to a thousand, from people thousand to 2009 for Office two thousand,
three thousand. Okay. Then we can match
the data using a VLookup. Now, lookup value is over here. Then where do we want to look it up on the first column
of this range here. Then we want to have to
as a column index number. And then we're going to have
now an approximate match. Now it returns of it. Why does it return office? Because 2499 is not here
in the first column. However, it goes to the
largest value underneath it, which is 2 thousand. Then goes to the second column where we have office
as the category, that is the value
that it returns. Okay, and now is this
possible with HLookup? Works exactly in the same way. What about AKS lookup? Well, of course,
it's also possible. Now, let's have a look how
this would work with facts. Look at that. I'm going to go back to
the very first example. And I'm going to write an
x lookup lookup value. Is that 750 that we have there? We're going to look it up here, the quantity column comma. And then we want to return
the value at year end, the discount rate, okay? Close your brackets,
press Enter and you see returns an error. Okay? Now, why? Because by default and excellent God
does an exact match. Now, if this would
have been a VLookup, V lookup by default does
an approximate match. However, you figured most of the times people want
to have an exact match. So therefore, exact match is now the default
for AKS lookup. And if you want to do
an approximate match, then we have another
argument here. If not found, match
mode and search. Now, f naught found that
could be harmful because it could an elegant
way return message. So I, their value not found. Okay? So you see, instead of an error, I get now the tax that is specified in the
formula, alright? And then there's another
argument. After that. I could specify comma, comma to go to the next one. And here we can say, okay, what kind of approximate
match do we want? We can say exact
match, that's 0. We can exact match or
next smaller item, exact match, or next larger item or wildcard character match. Now, over here, the ones that we're interested
in are these two. We want to have here
an exact match or next small item want to go
to the one that's below it. And so the largest value below the lookup value
basically, okay, now selected by pressing Tab, and then we can
close the brackets, press Enter, and
it returns the 4%. Now sometimes there might
be a use case also of one, which would then return the six per cent that's right
above the lookup value. So then it would go to the thousands and
then to the right. Okay, So you have a
little more flexibility here compared to the VLookup. I'm going to return
over here the 4%. Now how would that work
for the other ones? Exactly the same way. Now that you know what an
approximate match it does, you're probably a little bit disappointed because
often you want to look up the lookup value
in a range and find the value that
almost the match. And that is not what an
approximate match does. Instead of that, we
can solve that problem with wildcard characters
and do a contains much. Now, that's going to
be the next part.
7. Wildcards for more flexibility: We have just seen
how the approximate match works and when
you want to use it, however often you also just want to take the lookup value, look it up inside of the lookup column and f There
is almost the same there, then match the data that is not want an
approximate match does. So how to solve it? Well, with wildcard character, that is exactly what we're
going to look at right now. Now, follow me to the
sheet 05 contains. Now, here we have a very
small dataset and what we want to do is take
the company name, look it up here in
the company colon. And then once we find the value, then extract the amount
in the debit column. Okay? Now you see already
that Wayne is not here exactly in
the company colon. However, we have
Wayne Enterprises, Inc., which probably
should be matched. Okay, so let's see
how we can do that with our lookup functions
now, also here, we can do it, but if you
look up or an actual cup, Let's first do it
with a VLookup. I'm gonna go here to D6, enter an equals sign V lookup. We want to look up the value in the five when we have Wayne. We're going to look
it up over years, starting with column D. And then we want to go at least until
the debit column comma. Then once we find Wayne
Enterprises there, than we want to extract
information from column two. And for now we want to
have an exact match. Close your brackets and
doesn't find Wayne, and therefore returns
an error as expected. Now a lot of people think, you know what, I'm
going to fix this. I want to have an
approximate match. So change this to true. And it does give me a value. But this is the dangerous part because it gives you
the wrong value, the value for, well, Stark and this is not for
not for the one that you probably were thinking where the match would be,
Wayne Enterprises. Because it turns your value, you might think everything's
fine. You continue. Now, just because here the
dataset is very small, it's easy to spot the mistake. However, if your
dataset is very large, then it might not
be that obvious. Okay, now, how can we fix that? Well, let's go back
to our formula. I'm going to undo, fill color, go back
to the formula. And here we are going
to get rid of the five. And for now, just dive in weight between quotation
marks because it's taxed. And here we do not want to
have an approximate match, but we want to have
an exact match. Now at the moment still
returns an error. However, if you now
go back and hear you played an asterisk
sign right afterwards. Well, that is a
wildcard character, which means anything
can follow afterwards. Alright, so if I press Enter, you see now we get 63, which is indeed the value
that we were expecting. Okay? What if there's also
something in front of way? For example, here
we have a number and then doesn't work anymore. But then we can also place an asterisk sign
right in front of it. And that means now we are
looking at the value that just contains whatever is in front of it or after
it doesn't matter. Now let's try this also
for different companies. Let's say Holly, Okay, now here my formula doesn't update because a
hard-coded Wayne. So now I'm going
to hardcode wholly inside of the formula
and it works. Then we have the seminal 50. But sometimes you
need a bit more, needs to be a bit more exact. Alright, so now we
just say it should at some point contain, of course, we can either place it on
both sides or just one of the sides or they're
still not alternative. We can also say that
we are looking at early and then five
characters, okay? And that is the second wildcard character,
the question mark. Okay, So what the question mark? Now we are saying we need
to find Holly and well, five following characters and spaces also are a character. So if I know of a year that it should have five
characters following only, then question five
question marks. Okay, I see it works. However, if we would have
one extra character, then we return an error. So this one, the question mark is little
bit more restrictive. Now, at the moment, we are
hard-coding the lookup value, which is of course not so good. So how can we make
this so that we don't hard code the value
but just referred to D5. Well, we go back here. And then instead of
hard-coding value, we're going to
refer to only now, how do we get the wildcard
characters in while we want to have an asterisk
sign before and after it. So you go right before
the D5 Dacia mark, asterix quotation mark now needs to be in-between
quotation marks, otherwise, actual
things you're gonna do. Well multiplication.
Alright, and now we want to combine text, basically a wildcard character. Stacks do the value that's
inside of the five, which we can do with it
and Sinai ampersand sign before it and after
the cell reference. And then after it,
we want to have another wildcard character,
quotation marks. And that's it. Press Enter, you see, it works again and
finds holy there. Okay? And if we change it now to when you see it returns the 63. Perfect. So now we're not hard
coding the lookup value, but just referring to a cell that contains
the look of that. Again, you do this
also with AKS lookup. Of course again. Now let's go back and change
the VLookup to an X look up. Now, I'm not going to write
Donnelly from scratch. I'm just going to keep
that very first part. And so the lookup value,
that's not going to change, not the lookup
array is over here. And then we don't
array is over there. I'm going to close my
brackets, press Enter. Well, why did it work
with the VLookup? Another facts lookup. Well, because the x lookup, you specifically have to say that you're using
wildcard characters, which you can do by making use
of the optional arguments. So comma, comma. Then here at second
optional arguments, then we can say we are using wildcard characters for
the match presenter. I see now it works again.
8. INDEX MATCH | How it works: Now at this point
you have seen all of the most important
things that you need to know to extract information from a
dataset or two matched datasets together using
different lookup functions. However, there are situations where you need even
more flexibility, more flexibility than what the different lookup
functions are for you. And that's where index
match can help you out. Now, basically is
the combination of two functions, index and match. And if you put the two together, then you have even
more possibilities. Now, let's first do a normal
lookup using index match. Now for that, follow
me 206 and next match. Now here we have
that same dataset as what we have seen before with the different prices for different beverages and sizes. Alright? Now, let's say that we are
looking up again, creating t. Then for the size medium,
what is the price? Now, here we're going to
now use index and match, because these are two functions. Let's do them step-by-step. So instead of doing
it all in one cell, I'm going to split it
up into two cells. Now let's first go for the
index and then for the match, and then we put
the two together. So here we have index, there, we have match here. Let me create some placeholders. Let me make this
column a little bit wider and start with
the index function. Here, just dive in index. Now the index function
simply gives you the value that's at the
intersection of column and row. So first start with the range. So here we have a dataset
and this time it include the address that's important for later on because you
have to be consistent. So I include that is, and I would like to have
the value for its own row, well over here for green
tea, that's row 11. Then let's say we want
to have medium-size. So that is column number
1234 in that range, and then gives us 245 over here. Now of course, we don't
want to manually look it up and figure out what the
row and column numbers are. That's exactly where the
match function comes in. Now with the match function, we can look up, look up value, and color, just like the lookup functions
we have seen before. So the match function
will then tell you what the relative possession so the role in which that
look of the IRS. Okay. So I'm gonna use a match function
for the second one. So timing match
selected by pressing Tab lookup value is
going to be green tea. And then we go to look it up. Come on. We're going to look
it up over here in column B. And you see I include
that, That's important, that's consistent with
the index function and also included
the adder, okay, so just be consistent, either included in the index
function and include in the match function or excluded
in the match function or excluded in the
index function, okay? Then we want to have
an exact match, so 0. And then we can close
the match function. And you see it gives me
the row on which green tears and that value we can then use inside of
the index function. So instead of hard-coding, the unloving could just use the outcome of the match
function like this. Okay. So I just referred to the cell that contains the
outcome of the match function. No extra course that
gives me then to 45, the correct value, however
we needed to cells. So can you put it all together? Yes. Alright, so if we
combine the two, then we can just grab over here the match function without the equal sign for
control C to copy. And then you go back
to the index function. Then over here, instead of referring to the cell that contains the match
function at 17, I'm going to take that out. And then for the
row number based in the match function,
press Enter. And there you go. We have 245. Alright, that match function over here. We don't
need anymore. You can delete that. So here, this index
match combination does the same thing as a
VLookup aren't as an ax lookup. Okay, now, you
might be wondering, what is exactly the
benefit of doing like this seems
much more complex. And that's true. If you can do the same
with VLOOKUP and HLOOKUP, just go for the
VLookup acts locally. However, I will give you one or two examples where index matches
the only way to go. Now let's look at
a first example where index match can do
something that VLookup. Now, for that, I'm going to just readjust this
here a little bit. Now let's say that we're not
interested in the price, but we are interested in
returning D category, the product category in the 31st gotten that
you see over there. Now, if we would do
this with VLookup, we could say, if you lookup, lookup value, that's
the beverage. And then we look
it up over here. And we want to extract
what we want to extract the category which
is outside of the range. So that's where the problem is. So if I just dive in here, now, what is the column index,
minus one or minus? You cannot go outside
of the range. So over here I can finish it, but you will see that
it returns an error. It cannot go outside
of the array. So you might think, okay, but then we can just
adjust over here the range so that it starts over
here at the beginning. However, then it's
going to look up green tea in the very first
column will not find it, and also then you
will get an error. Okay, So this is not solving it. So what a lot of
people sometimes do is they would take that
product category column and then just drag it here
to the right hand side so that you can return
the value to the right. However, this is a bit clunky. Now, how can you solve this? Well, first of all, you should know that you could have solved it
also within AKS lookup. So we can do an excellent job. Lookup green tea, look
it up over here and beverage column and then return the value here to
the left of it. Press Enter. I see it is in D category. Okay, so this is probably
the easiest way to solve it, but now I want to do
it with an index match before we are going to look at more complex example
with index match. Okay, So how would that work? I'm going to start
with the equal sign. Now here we can
use again in next. Now, where is my range? Ranges over here. So the whole dataset, including data's, alright.
Then the row number. For the row number, I can use the match function. Match lookup value is over here. The beverage. Where do we want
to look it up in column B, including the headers. Then we want to have
an exact match to 0. Close the match function.
So the match function is going to figure out
the row number comma. Then from which got them done. We want to extract
the information. Well, over here we can extract the information from the
first column in the range. So one, close the
brackets, press Enter. I see also works. So probably now you think, yeah, but the actual lookup
was still easier. I agree. However, in the
next two examples, we're going to
look at two cases. We can only solve it
with index match. So let's have a look.
9. Row and column lookup: You just add a
first introduction to the index and
the match function and how you can use
them to get it to basically do the same
thing as an actual logo. So what's the point? And this part,
we're going to have a look at how you can do a double lookup lookup value
in a column and a row. Now let's head over
to the sheet 07, row and column lookup. Now here I want to
look up an account in that very first column
with the account numbers. And I would like to
look up a certain year because we have values
here for the year 20202122, and that is the value
that I want to return. So in this case, 6,805 is here. I want to return
the value of 2020, which is there, which
should then be 562. Important is that both
the account number and the year need
to be flexible. So I want the end-user to put in a value here and here to then get the value over
here without having to play around with
the formula itself. So how can we do that? Well, this case can only be
solved with index match. Now, just like before, we can split it over
multiple cells. So we're going to have an index function and we're going to have a match function. But now for the row
and the golden, alright, so we're going to
have to match functions. And over here we can start
with the index function. And we can say gay, the dataset that we're
working with is over here. And here either include
or exclude the headers, but be consistent. Alright? So I'm going to
include the others. Then. Just for now, I'm just
going to say, Okay, account number 6,805
is in row number four and then
column number 1234. Okay, So for, for some
hardcoding values, and that gives me than the 562. But of course this
needs to be dynamic. So you have to figure out on what row 6,805 their calendars. We can use the match function. I'm going to type in match. Now what is my lookup
value? The 6,805. Where do we want to look it up? And then very first column, and we include the
headers or expert data's, just be consistent and you
see it gives me a four. Then the same thing
for a garden. So over here we can use
another match function. The lookup value is the year that we have right below
the account number. And where do we
want to look it up? We want to look
it up in the row. Alright, now, here it's important that you start
at the beginning, right? Because here, in our
case it needs to return 1234 column for the match
type needs to be exact. So 0 Enter, I see
it gives us a four. So now we have the
three components and we just have to
put them together. We have over here
the index function. And for the row number
its than hard-coding it, we can refer to the
cell that contains the outcome of the
match for the row. And then for the column, we have over here a reference
then to the match for God. Alright? Still the same. However, we need for results. We want to have everything
in one formula. So instead of just
referring to that cell, I'm going to copy that
match function Control C. Go back to the index function. And then here go to this second argument
for the row number D9. I'm going to delete based
N here, the first match. It still works. So you see that
one we don't need anymore than we do the
same thing for the colon. So copy over the match
function over here, two into that index. Then they're enter steelworks. So now that second match
function we also don't need. And what we're left
with this formula. Well, that is our
double lookup formula. So I'm going to copy that one. And then basically
in here for D6, That's the final result. Alright, Now you see quite as many functions and might be a little bit
overwhelming at the beginning. However, just make sure that you split it
and at some point, once you have done in
them, multiple times, you can do it in one go. Now what also might make
it a little bit easier is to divide it
over multiple rows. So if we make the formula bar a little bit bigger than here, we can use Alt Enter to place the index function
on the next line. The same thing you
do then for all of the different arguments so
that you don't lose oversight. What is doing, what, and where are the opening and closing brackets
for what function? Then here, the middle part, the middle arguments
for index function. I probably would end
and a little bit so that it becomes a
bit more readable. Disliked it. Press Enter, you see spaces, and placing new line doesn't affect
the functionality. And what is also nice when you combine this with drop-downs. So if I make the
formula bar and live in Lazada and go here to
the account number. So then we can make use
of data validation. If you go to Data. And then here, data validation, then you can say what should be allowed for the user
to put into that cell. Now, we only want to have the values that
are in the list. Then for the source, here we have unique values and
the account number column. Press Enter, click. Okay. Now we have a drop-down so the user can choose
which account, for which account they want
to extract the information. For example, 6,022. For years, we can do the same. Select the ESL data
data validation. And also here we
want to have a list with the values that are over here in the row
header. Click Okay. Now we have two drop-downs and
it can change over a year, the year, as well as their gut. So if I chose, choose over here, 6,818, which is over here. Year 21, which is over there, we have 8,038, which is correct. Perfect. So in next match is working. We are now doing
a double lookup, which you cannot do with a
VLookup x lookup or a job.
10. Multiple column lookup: Let's look at another example
where you need Index Match. I cannot use one or the
other lookup functions. And that is if you want to
do a multiple colon lookup, now follow me to sheet number eight, multiple column lookup. And here in this dataset, I have above it
the lookup value. So you see we have
the year, the month, the barn ID, and
the productivity. And basically there's no
unique value in the dataset only when I combine
multiple columns. So the columns for the
year, month, bargainer, and product ID only when I
combine them on it done, we get a unique value. Okay? Now let's first write a formula and then go
through it step-by-step. Because I think it's
a little bit easier when you see the end result. Alright, now we're going to look up the cost-per-click
for product one, partner ID, one month,
January year 2010. Alright, so how to do that? Well, the value that
I'm expecting is yet at $0.72 in that first row. Okay, now, let's
give this a try. Let's go to age nine. And here we can write
index function. Now. Here, first of all, where is the table from which I want to extract
the information later? Well, we have our
dataset over here. And also here. Just be consistent if
you execute the others. Select the whole bridge, okay? Now what is my row number
instead of my reference to H7? Let's put in to the column
number that's put in then six. And then we can close
the index function. Presenter $0.72. Alright, easy. But now what's next? Now we need to figure
out in which row, the combination of these four
lookup values, S, right? But then in a dynamic way, instead of hard-coding that too. So for that we can
use a match function. This is where it gets a
little more complex because now the lookup value
consist of that value over there and that value over there, and the value over
there and there. Alright, so we're
going to combine the tax that is in
these four cells. Alright, comma, then
the lookup array, we want to look it up. Well combining, well
again for collapse. And here, because essence, we include the arrows for now. So I click here on D
or Control Shift down, and then the ampersand sign
so that we can combine it. We have the next column
right next to it, okay, which is the month. And then recombine that one with the partner ID over here, control shift down,
not an ampersand sign. And then the last column
we've got over here, Let's go to the top Control
Shift Down to take that one. Okay? So that is the lookup array, the combination of
the values that are inside of these
four columns, okay? Then the match type, what we want to have
an exact match 0, then we can close
that match function. Well, I see, I have,
they're wrong. I can get rid of that
and press Enter. You see we have $0.72. What if we have producto? Let's dive in here. Products, do they see $0.64? Okay, so it's working, but why is it working? Now, let's go over the
steps step-by-step. So let's go here to the
right-hand side of our dataset. Now how did we start? We started by saying, okay, we don't have
a unique value, but only when we combine
the air with the month, with the bargaining
with the product. Okay, so we combine these four surveys which
gives us one value. Then where did we
want to look it up? Well, there is not one column
where we will find that. However, we can combine the values that are in the
year column over here. Then the month than the
partner, than the products. We combine them and
then we drag it down for the entire dataset. Once you have done that, then we do have
one unique value, which we can then find where it is using a match function. So match, this is the value, the lookup value, where
do you want to match it? But in that column
that contains all of these combined values
from these columns. Alright? Then also here we want to
have an exact match 0. And that gives us a tool, and that is indeed correct. We have over here product to select that if this
will be product one, then it would return
over there, the one. Alright, so this is how we can
do multiple column lookup. However, now we just
have the row number. We need to have
the actual value, the $0.72 over there. Know how to get
the actual value. Well, we have to do an index, okay, so we can then say index. Now we're, we have
that old dataset. So we take over here
that whole dataset. Then we have the row number. Well, the row number we
just figured out here. And the column from which we want to extract
the information, cost per click is a calm sex, and then close the bracket
that gives us the 72 sets. This is how it
works step-by-step. Alright, so going back to our formula where we
have the whole thing, let me make this a little
bit bigger and then put it again on separate lines to make it a bit more readable. So we say index, not the range part,
that's the easy part. Now, from which role? That's where it gets tricky, way more bit more difficult. And over here, that is, the last part is just the column from which we extract
the information. Okay, So here the middle
part is the tricky part. So we combine the lookup
value's recombine columns that we use for a
lookup over here. And we want to have an
exact match, and that's it. Alright, so this
was another example of how you can use
index match to do something that you cannot do with VLookups are x look-ups. Now of course it's very
specific, however, might be very
helpful to remember this once you run into
a similar situation. So that's it. Now you should be able to tackle any situation for which
you need to do a lookup.
Bas Dohmen, Founder + YouTuber
