Articles Blog

Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples

Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples

Welcome to Highline Excel
2016 class video number 11. Hey, if you want to download
this workbook, Business 218 video 11, or the PDF
files, click on the link below the video. Oh, this is going
to be an epic video. This is all about lookup. In the PDF, there’s 18 pages
of amazing lookup notes. Now, let’s go over to
our Excel workbook. There’s 20 examples
we’re going to see. And these examples
will span pretty much every type of lookup situation
you might find yourself in. Now we’ll start with
VLOOKUP, and then look through many different
types of functions, like INDEX, MATCH,
CHOOSE function, and we’ll see many types
of lookup formulas. Let’s go over to
the sheet, Lookup. Now, for our first
example, we want to look at the VLOOKUP
function, using exact match. And we want to talk
about data validation. We’re even going to see the IF
and ISBLANK functions to help us build an invoice solution. Now here’s our situation. Here’s a column for price. So we’re going to
need prices here. Any time we put a
product name here, we want the price to show up. Our products, these
are boomerangs. So we have a Carlota
and Sunshine. When we look up Sunshine,
we need our LOOKUP function in this cell right here to go to
the first column of the lookup table, find the row
that contains Sunshine. And then we need the LOOKUP
function to get that $18.95, and bring it back to the cell. Now, any time we’re
doing exact match, and we have our first column
with items we’re looking up, we should use data validation. So I’m going to
highlight these cells. And up in the Data
Ribbon tab, Data Tools. There’s our Data Validation. I’m going to click this. Or I’m going to use
the keyboard Alt, D, L. The default is to
allow any value. We don’t want that. So we click the dropdown,
and we want to select List. And then in the source, we
highlight the first column of the lookup table. This is a list of items. And now for each
one of these cells, we’re only allowed to
enter items from this list. Luckily, there will
be an in-cell dropdown when I click OK. Just like that, now I can
select from my dropdown. If I try to type something
that’s not in that list, I get an error. I’m going to click Escape. Delete. So data validation, now
we can use our VLOOKUP, equals V, L, tab. That V is for vertical. The first column has our
items we’re looking up. And they’re listed vertically. Lookup value, that’s
the item we want to look up and try and match in
the first column of our lookup table, comma, the table. Highlight not the field names,
just the first column and all of your subsequent columns. We’re going to copy this down. So we need to hit the F4 key to
lock it, comma, column index. Since we want price, you go up
to your lookup table and count. 1, 2, 3, 4. So the fourth column in our
lookup table contains price. So I put a 4 here. That 4 instructs VLOOKUP
to go to the fourth column, get the price, retrieve it,
and bring it back to the cell. And that’s opposed
to– if we were trying to look up flight range,
we’d have to give it a 3. If we were trying to look up
part number, it would be a 2. So we have our
fourth column, comma. And then we have
approximate match. That is for taxes,
commission rates, or when the first column of
the lookup table is sorted. Exact match is for when the
first column of the lookup table is not sorted. And it will take whatever
characters, numbers, or letters that are in the cell. In this case C-A-R-L-O-T-A.
And it will do a linear search starting at the top, and it
will check every single one. So in this case it will
check Bellon against Carlota. That’s not a match. Carlota against Carlota
that is a match. Now we can put a false or
a 0 for approximate match. We could put true,
1, or omitted. So for exact match,
I always put 0. All this argument does
is instruct VLOOKUP which of the two types of
lookup it should be doing. All right. Now we close parentheses. Control enter. That is looking good. If I come over and
change this to Quad, instantly VLOOKUP gets
the corresponding price from the fourth column. Control Z. Now I’m going to
copy this down, click and drag, and sure enough VLOOKUP
is being polite here. It’s delivering an
N/A, which means, hey, I tried to look up
nothing and I couldn’t find it in the first column. So it is not available. Now there are a few ways that
we could solve this N/A problem. Let’s look at a few
different ways over here. IFNA, and this is
really convenient, because the value here
VLOOKUP, IFNA is programmed to use whatever
VLOOKUP delivers, unless it delivers an N/A. Then
it comes to the argument value IFNA, and it uses the syntax
for show nothing, which is a 0 length text string,
double quote, double quote. Alternatively, we could
have used IFERROR also. Now these are fine, especially
on small spreadsheets. But I need to show
you an alternative. And here’s the reason why. There are many
workbooks out there in the working world with
hundreds of VLOOKUP formulas running in the workbook. Not only that, but sometimes
the lookup tables are gigantic. And so VLOOKUP, especially
when it’s doing exact match and it needs to check
linearly every single one, if the formulas are calculating
across thousands of rows and there’s hundreds
of VLOOKUP functions, your workbook calculation
time can slow down. So any time you have an
alternative logical test, because over here we’re
running VLOOKUP to figure out if there’s an error or not. So if you have an
alternative test, which is potentially shorter in
calculation time than VLOOKUP, then you want to do it. And we do in this case. As we copy the
formula down, we could be asking the question,
hey, is the cell empty? So I’m going to
come up here, F2. And even though
this actual formula will be longer and take
more time to create, it’s the calculation time
that we’re worried about. So now we can do equals
IF, and in the logical test I use ISBLANK. Now remember, they didn’t
name this function smartly. ISBLANK only checks
if the cell is empty. That means nothing in it, not
even a zero length text string, close parenthesis, comma. And in the logical test
we have an alternative. It does not have to
run VLOOKUP every time. What it’s going to do is
it’s going to run this. If it is empty, then we
come to value of true, and put our syntax to show
nothing, double quote, double quote. Otherwise, if the
ISBLANK function comes out false that means
there’s something in the cell. Then it runs VLOOKUP. The beauty of this
for spreadsheets that have slow calculation time
is it’s only checking this. And if it comes out
true, that’s all it does is dump
that into the cell. It never gets to
calculate in the VLOOKUP. So that is a useful alternative. Control enter, double-click
and send it down. And by the way,
those kind of details are over in the PDF notes if you
download those, and read them. All right. So now we have what we can
come over here and test. I’m going to say Quad,
and instantly it shows up. And we’re going to
buy 10 of these. Now we need to
calculate our total. We’re never going to have
extraneous decimals when we are multiplying price times units. So we’re not going to
use the round function. I’m simply going to
enter this multiplication of two cells, Control
Enter, and copy it down. Now notice what
happens, a value error. Hey, we got N/A over here. Why are we getting value? Because, guess what. This formula delivered a 0
length text string to the cell. This cell right
here truly is empty. And any time a formula
has an empty cell which evaluates to 0, which
is a number, times some text, even if it’s a zero length text
string, you get a value error. So in this case, we’re going
to do exactly the same thing. Now watch this. I’m going to cheat. I’m going to copy this whole
little thing right here, Control C in edit mode, escape. Now I come over, here F2. And right at the beginning
after the equal sign, I’m going to
Control V. I’m going to run the same test, close
parentheses, Control Enter. And I’m going to copy it down. I’m not going to double-click,
because we have total there. So there we have our
invoice solution. We did data validation
dropdown list, we did VLOOKUP exact
match with IF and ISBLANK, and we even had a
multiplying formula. Let’s go look at
our second example. Now we just did
vertical lookup, and we do want to take a moment to
talk about horizontal lookup and the HLOOKUP. The H means horizontal. Now, most tables are
not set up this way. But occasionally
you’ll find a table where the lookup value is
in the first row instead of the first column. So in this case, we want to
look up Gigi, and find her phone number. So I’m going to use H, L, tab. Same exact arguments, well
almost, as the VLOOKUP. Our lookup value, I’m looking
up Gigi, comma, table array. Well, it looks like
the same argument. But when I highlight
this, the HLOOKUP will try and find Gigi
in the first row, comma. And instead of column index
number, it’s a row index. So we have to count. 1, 2, 3, 4, 5. The phone number is in
the fifth row, comma, and I’m doing 0 for exact match. So that is the
rarely used HLOOKUP. If I change this to
Chin, instantly I get my telephone number. Now we want to go down to
talk about our next example. Its VLOOKUP with
approximate match. So here’s square
footage of a lot. And we have our first column
in the lookup table sorted from smallest to biggest. Approximate match will
not work correctly, unless it’s sorted
smallest to biggest. And we need to return the pipe
diameter to use for drainage. All right. So you ready? Equals V, L, tab. Our lookup value that’s
going to be square footage for this lot, comma table. First column is sorted. Subsequent columns have things
we can go and get, comma. Pipe diameter is in the
1, 2, the second column. So I put a 2. And now I type comma,
and the lookup type is approximate match. You can use true. You can use 1, or you
can leave it omitted. I always leave it omitted. Because why even
go to that argument if you know the default
is approximate match? We don’t need to put it in. Close parentheses, Control
Enter, and there it is. We need a pipe diameter of 25. If the lot is 2,500, instantly
VLOOKUP gets a 10 inch pipe. Now how does approximate
match VLOOKUP work? Well, as a metaphor,
here’s how it works. VLOOKUP takes that
2,500, and it keeps racing through the
sorted first column until it bumps into
the first item bigger. And then it jumps back. And that’s how it
knows which row to take the pipe diameter from. Now technically what it
does, it does something called a binary search. And I actually have
a video over here of how binary search works. It actually divides
the set in half, and successively searches
smaller and smaller data sets. That is why the approximate
match VLOOKUP can sometimes dramatically calculate more
quickly than exact match. So really, if you ever have
trouble with slow calculation times for your
VLOOKUPs, if you can, sort the first
column of your table, and use approximate
match lookup. Now what happens if we
type a value that’s bigger than the last one, 75,000? Well, approximate match
takes that 75,000, and it races through, and if
it can never find a bigger one, then it just takes the last one. The only situation where we
get N/A for approximate match is if you put something
smaller than the first value in the first column. Most of the time you
could avoid that N/A by choosing this first
value in the first column so that it’s smaller
than any value you could potentially have. And in many cases, like
this square footage, we’re not going to have
minus square footage, 2,500, Control Enter. Now, let’s go look at our next
example for approximate match with VLOOKUP. Jo had $8,000 sales,
and we need to look up from the third column of this
lookup table, Jo’s commission paid, equals V, L, Tab. I’m going to look up 8,000,
comma, the table array. There’s the first column
sorted, subsequent columns have things I want to
potentially go get and bring back to the cell, comma. It is 1, 2, 3, the third column. I’m going to leave
that last argument out. Because I know the default
is approximate match, Control Enter. And Sue gets $250. If her sales were not 8,000,
but actually 6,999.99, oh man. Look at that. She just missed it by a penny. Now let’s go look at
this same example, but here we want to look
up each sales rep’s sales, and get the rating and
the commission pay. So here’s our lookup table,
and I want a single formula that I can copy down and over. Now, in this case
rating is in column 2, and commission is in column 3. So instead of
creating the formula, copying it down and over, and
then editing it and changing the column 2 to 3, I went ahead
and put the column index number at the top of each column. Now we’re going to see a number
of different ways of using VLOOKUP to extract multiple
things from columns. This is one of the common ways. Hey, just put the column
index above each column. So are you ready? Equals V, L, tab. I’m going to look up the sales. But when I copy it to the side
I need it locked, when I copy it down, I need it to move. So I hit the F4
key 1, 2, 3 times to lock the column but not
the row, comma, the table. There’s our table. And I’m going to hit F4 to lock
it in all directions, comma, and the column index number. For this column when I
copy down, I need the 2. But when I move to the side,
I need that C to move to D. So I hit the F4 key
1, 2 times to lock the row, but not the column. I do not need the last
argument, because the default is approximate. Close parentheses,
Control Enter. I’m going to copy
it to the side, and then double-click
and send it down. I’m going to go to
the last cell, F2, to verify that all the cell
references are correct. Now using column index
numbers typed into the cells is one method to
help VLOOKUP when it has multiple column indexes
to help it extract data. Let’s go down and look
at some other examples. Now in this example we have
a human resources data set. We have ID, last name, first
name, email, and phone number. Now this is a pretty small human
resource employee data set. Sometimes an employee data
set can have 20-30 columns. And in many cases we need
to look up an ID or a name, and extract certain bits
of data for that employee or for that record. Now here I need to get
first, last, and email. So what would that mean? If I was using
VLOOKUP I would have to have 1, 2, 3, so column
index number would be 3 here. For last name it would be
1, 2, so column index 2. And for email, 1, 2, 3, 4,
column index would be 4. Now there’s no problem. We could type the numbers
right above our foil, like we did our last example. But in many cases you cannot
have those numbers typed into your cells, or you want
an automatic method inside our formula that will know how
to go and find first name, 1, 2, 3. No problem, there is a function
called the MATCH function. Now the MATCH function is
our second lookup function. And notice the
arguments look similar. Lookup value, well guess what. MATCH is not going to actually
go and retrieve something. When I say, hey MATCH,
please go look up first. And watch this, comma,
the lookup array is going to be our field names. All MATCH will do is
it will look up first, and it will count 1, 2, 3. It’ll say first is the
third item in the list. So the MATCH function will
always look up an item and find the relative
position in a list. So notice the relative positions
here could be 1, 2, 3, 4, 5. But because first is
in the third position, that’s what MATCH will report. And notice that is exactly
what we need for VLOOKUP column index number. Because first is the first,
second, third column. Now we need to come
down here, and I’m going to lock this with the F4 key. And now, I’ll come to the end. And we have to learn about
MATCH’s different type of lookups that it can do. Now notice, it can do three
different types of lookups. Now the last one we’re
never going to use. But luckily the first two,
and they don’t call it approximate and exact match. But these first two are
exactly like VLOOKUP. If you put a 1 or omitted,
it will do approximate match lookup, just like VLOOKUP. And if you put a 0, it’ll
do exact match lookup just like VLOOKUP. Now notice, when I say exact
match that means it’s going to look for exactly the
characters F-I-R-S-T. All right. So that’s the MATCH
function, close parentheses, Control Enter. And there it is. It reported the
relative position. When I copy this to the
side, notice right here. It’s looking up last within
that range right there. And it’s reporting, yes, it’s
in the second relative position. Email is in the fourth position. That’ll be perfect for
VLOOKUP’s column index number. All right. So let’s try this. Now I’m not going to
eventually leave that there. Because in many cases you
can’t have those extra cells. So right here, we need to
look up the ID with VLOOKUP. And VLOOKUP needs to
know from the field name right above the cell that that
should be the third column. So watch this, equals V, L, tab. Our lookup value
is going to be ID. And I’m going to lock it with
the F4 key, comma, the table array, I’m going to
highlight the table. F4 to lock it, comma,
column index number. I want to automate
this by using MATCH. So right in column index,
I’m going to type MATCH. Now watch the screen tip. It’s very polite. It will change for us. Lookup value, that’s
going to be the field name right above the cell
where the VLOOKUP is. So we have our lookup value,
comma, and the lookup array. It’s going to be this
range right here. By the way, for MATCH,
because it’s always looking up the relative position
of an item in the list that can only be a one-way. That means it has to be a
single row or a single column. You could never give–
lookup array an entire table like you would with VLOOKUP. Now I’m going to lock
this with the F4 key, come to the end, comma,
0 for exact match. And now here’s the screen tip. When I close parentheses,
the screen tips are polite. VLOOKUP pops back up. Column index number, that
means the whole match is simply going to find
the relative position, and return the column
index number for VLOOKUP. That is pretty amazing. Come to the end, comma. Oh, and here’s the two
options for VLOOKUP. Which type of
lookup are we doing? We’re doing exact match. I’m going to put a 0. And by the way, that’s
why I like to put a 0. Then I just have to remember
0 for either the MATCH or the VLOOKUP
means exact match. Close parentheses, Control
Enter, copy it to the side. Come to the last
cell, and hit F2. That is pretty beautiful. That is amazing. Now, I’m going to
delete these right here. And another benefit to using
VLOOKUP and match function for column index is if we
were to ever change this, this should actually
be last name. Instantly, because MATCH is
looking at that field name and looking it up, VLOOKUP
gets the second column. If I change this to first
that is pretty amazing. This is oftentimes
the preferred solution when you have many
fields you are looking up within a particular record. All right. Now I’m going to Control Z,
Z, Z. I’ll leave that there as a trail of what we did. Now in this case
notice, these columns were not in the same order. I wanted first, last, and email. But in some cases, you actually
want to look up this ID and return the entire record. Now, I’m going to show
you another example of how to do this with VLOOKUP. But I want to hide these rows. So I’m going to click on
row 84 all the way to 92, and then a right-click Hide. I’m hiding rows. Now here’s the situation. Where you have the ID. And we do not have field names. And I want to simply list
the record either vertically in a column filled with
different rows, or over here I have only the
ID, no field names, and I need to list the
record horizontally. In both cases we can use
either the ROWS function or the COLUMNS function
to get our column index number for VLOOKUP. Now we saw both ROWS and
COLUMNS back in our video on references. Let’s remind ourselves
how ROWS works. ROWS, if I gave it this
many rows, notice 98 to 100, ROWS would simply report
that it found three rows. Now what we need is
we need to use this as we copy down to expand
to give us 2, 3, 4, 5 column index. So watch this. I’m going to highlight the
cell right above with the ID, and the actual cell
with the formula. And I’m going to come
and click on A97, and then hit the F4
key, 1, 2 times to lock the row, but not the column. Now notice what ROWS is
getting right now, 97 and 98. So ROWS will report a 2. But when I copy down, because
the first reference is locked but the second
one is not, it of course remains locked on 97. But that second row
reference is free to move. And so we get an
expandable range that gives us exactly the
column index numbers we want. Click in the top cell, F2. Right after the
equal sign, V, L Tab. Our lookup value
is going to be ID. F4 to lock it going down,
comma, the table array. There’s our entire table. F4 to lock it. comma. And there it is. The ROWS function to deliver
column index numbers. ROWS always works when you’re
copying down across the rows. When you want to list
the record vertically, comma, zero for exact
match, close parentheses, Control Enter. Double-click and send it down. Go to the last cell, F2. That is amazing. Now the only
difference when we’re copying across the
columns is that we’re going to need COLUMNS
instead of ROWS. V, L, tab, I’m looking
up ID F4, comma. Here’s our table. F4 to lock it, comma. And in column index
number, I’m going to use the COLUMNS function,
which counts columns. I’m going to highlight
the ID and the cell the formula sits in. By the way, one of the only
formulas and functions in Excel that can actually
contain the cell D97 that the actual
formula sits in, and not get a circular
reference is COLUMNS and ROWS. Close parentheses,
and very carefully I’m going to click back
on the C97, and hit the F4 key 1, 2, 3 times. I’m locking the C in
the first reference, but not the D in the
second one, comma. Lookup exact match, 0, close
parentheses, Control Enter. Copy it to the side. Go to the last cell, F2. That is looking good. So when we need to
get all of the data for a particular record and
display it horizontally, we can use COLUMNS; display it
vertically, we can use ROWS. All right. We want to go look
at our next example. Now our next example
is a tax example. Here’s our taxable earnings. And we have to use this
somewhat complicated tax table to calculate our
total tax down here. Now the way a table
like this works is look at this first column. In order to figure out what
row you’re going to be in, it says over. Then the second row
says but not over. So this amount right here is
over 9,844, but not over this. Then from the third column
we need to get the tax rate. Then from the fourth
column, we need to get the tax that’s
already been calculated from these earlier tax rates,
and bring that back here. And finally, because some
amount has already been taxed, we need to take the
actual taxable earnings, and from it subtract the
earnings that have already been taxed. Now the problem with
this table, and this is like straight from the
IRS, is this first column will not work for VLOOKUP. That number right there is not
included for this category. So you have to recognize that,
and create an additional column here. Now we’re going to
start always with 0, and then I’m simply
going to make a formula. And since we’re
dealing with money, then the smallest
unit is a penny, I’m going to add one penny. And just like that we
have a first column, where for this row
right here that will be the smallest
amount all the way up to and including this. But for our VLOOKUP, it’ll know
that’s the first value bigger than our lookup values. So it will know to jump back. Now the first three
calculations are just three separate
VLOOKUPs, equals V, L, tab. And I’m going to look up taxable
earnings, comma, and here’s my table array. And I’m going to highlight
the entire table. I don’t even need the last one. I don’t have to
include that, comma. And I’m going to cheat. I’m actually going use this same
little bit a few other times. And column index, well this
is tax from the previous bracket 1, 2, 3, 4, so it’s 5. I put a 5. This is approximate
match lookup. So I don’t need
that last argument, close parentheses, and Enter. And that is the correct
taxes we’ve already paid from the previous bracket
where 28% will be our rate. Now for tax rate,
Control V, 1, 2, 3, 4, so column index is going to be
4, close parentheses and Enter. Control V, this is
amount already taxed, 1, 2, so I put a 2
for column index. Close parentheses and Enter. Now that amount has
already been taxed. So of course, we want
to take that amount and subtract it from the 15,000. So I take the 15,000,
minus the amount that’s already been taxed, and Enter. And finally our total tax,
and I call this total tax. So I’m going to
say, hey, there is the amount that’s
already been calculated from previous brackets. Plus– and I am going
to use the round. These are percentages
and decimals, and we might have some
extraneous decimals. The number, amount to tax, this
bracket times our tax rate, comma. And I’m going to do a 2, even
though in some situations, you might need a 0, close
parentheses, Control Enter. And there is our tax. Now, we can change this to
whatever the taxable earnings are, 5,874, and instantly
everything updates. Now actually this is
a standard tax table. And most standard
tax calculations are already built
into premade programs. But I had a
situation where I was doing– this was a few years
back– we were doing taxes. And the program
we were using was programmed to do all of
our tax calculations. But at the last second,
the law changed. And there was a
big complicated tax calculation like
this we had to do, and it wasn’t preprogrammed. So we came over to Excel, and
made a spreadsheet like this. Now in some situations
you don’t want all of these intermediate steps. And we’ve seen a few
times in this class, if you want to do one
formula in a single cell, it’s beneficial to
do it step by step. And we see our
formulas over here. So you see how each
piece should look, and we can see this
final solution over here is looking at B16 right there. But that’s the VLOOKUP. This round, it has
the amount in B119. That’s right here. But remember that amount
is taxable earnings minus this VLOOKUP. Then finally we have B117. That actually is the tax
rate, which is that VLOOKUP. So with this as my guide,
I could come down here. And I’m going to actually
Control Z to leave that. And now in this cell, we
can try to piece together this entire formula, equals. Well, I can’t click there. I’m going to Control V. Because
I still have that loaded up. The first VLOOKUP was tax
from the previous brackets. So that’s a 5. And I’m going to put
5, close parentheses. Then I’m going to use plus,
and I’m looking right here now, round. And I’m going to have
to in parentheses say, hey here’s the full
taxable earnings, minus, there’s the
amount already taxed. So it looks like the
VLOOKUP with a 2. Control V, 2, close parentheses. Now I close parentheses,
because that number argument. We need to force that
subtraction before we multiply. And we need the tax
rate, which is VLOOKUP 4. So Control V, right here,
4, close parentheses. Now I have all of that. And I know I need a 2. So I come to the end,
comma 2, close parentheses, Control Enter. Look at that. If I change this to 5,874,
instantly it all updates. This will do it
in a single cell. And this one did it
in multiple steps. All right. Now we want to go look
at our next example. Sometimes we’re given
a particular ID. And I want to keep it there. But I need to look up from the
product name, the actual price over here. So earlier this week we
learned about text functions. We can use the left
to, from the left extract a certain number
of characters, comma. It’s a variable amount. So I’m going to try
and search for a dash. So I’m using the
SEARCH function. Find text, double quote,
dash, double quote. I’m going to find that
comma within this. SEARCH will report that it’s
in the seventh position. So when I close parentheses on
SEARCH, that’s one too many. So I have to subtract
1, close parentheses. So that left search minus 1 will
get the correct product name. Now I need to put that
left search into VLOOKUP. So the lookup value
is simply a formula extracting what we need to match
something in the first column, comma, table array. There it is. F4 to lock it. Comma, column index, it is
2, comma, 0 for exact match, close parentheses,
Control Enter. And copy it down. Now, I just added an
extra column here, and an extra lookup table. Because a very important problem
that happens with VLOOKUP sometimes is notice if our
product ID was actually 2, 3, 4; 3, 4, 5, and we needed to
look up in this lookup table to get the price. Let’s just see
what would happen, equals mid– and I’m going
to say the text over there on the start number. We’ll do SEARCH again. And we’re searching
for a dash within this. Now that’ll give us the
position of the dash. And for the start position,
we actually need to add 1, and luckily comma, the number
of characters are always 3. So when I enter this
text extraction, double-click, and
send it down, we can see a visual cue about
what the potential problem is. Because this is
aligned to the left. These numbers are
aligned to the right. But let’s just pretend
like we didn’t notice that. And after the equal
sign, VLOOKUP, there’s my lookup value,
comma, the table is over here. F4, comma, 2, comma, 0, close
parentheses, Control Enter. We get N/As all the way down. And the reason why is
because of the data mismatch. If we come here
to VLOOKUP values, if I hit the F9 key to
evaluate, double quotes means that Excel
thinks it’s text. You cannot match a text
number to an actual number. Control Z. But we learned
a few times in the class so far, if we have
a text number and we need to convert it
back to a number, we do any math operation. So I’m going to plus
0, Control Enter. Double-click, and send it down. If I come back to
this cell, F2, if I highlight just the mid part,
and F9, we can see it’s text. Control Z. If I highlight
the mid plus the 0, F9. Boom, we’ve converted it
back to an actual number. There is now no data mismatch. Escape. All right. So sometimes with
VLOOKUP we have to extract certain bits of
data as our lookup value. And certainly be
aware that there may be data mismatches
between text numbers and actual numbers. All right. Let’s go look at
our next example. Here’s our next example. We’re selling boomerangs. And a customer
comes in and says, hey, I want a boomerang
that flies 60 meters. If we’re doing this by
hand, we have no problem looking through the description
column finding the 60, jumping over to
the first column, getting the Weighted
Majestic Beaut, and bringing it
back to the cell. But VLOOKUP cannot look up
something in the third column, and return something
from the first column. Luckily, there’s
the INDEX function, and the INDEX and
MATCH function together are the most versatile
lookup functions. Now let’s see how this works. Equals INDEX, now
INDEX has two options. And we’re only going to use
the first one with array, row number, and column number. The array are all of
the potential values you want to go and get and
bring back to the cell. And then you simply give
it either the row number or the column number, or both
if you’re doing two-way lookup. So the way you
remember array that’s the potential values you want
to go and get and bring back to the cell. All right. Ready? Comma, row number. Well, we can do it manually. We can see the 60
is in 1, 2, 3, 4. And I can even come
down here and type a 4. That’s not what we want to do. But it illustrates
the power of INDEX. We just give it the
items I want to look up. And as long as it knows
the row number, 1, 2, 3, 4; it will return the
Weighted Majestic Beaut. Now F2. That’s not how we want to do it. Because we want to be able
to change this here, and have the row number
automatically change. Of course, that’s the perfect
job for the MATCH function. MATCH has no problem
looking something up, comma, and the lookup array. It can be either vertical
in this case, or horizontal. But MATCH will find that 60
within this range, comma, and we’re doing an exact
match, close parentheses. So even though MATCH is
sitting inside of row number, if I were to F9,
there is that 4. Control Z. The power of INDEX
and MATCH comes from the fact that they can do any lookup,
including this type of lookup, finding the relative
position here and then returning something
from a column to the left. VLOOKUP can’t do that. Control Enter. And there it is. If I come over here from
the dropdown and select 80, instantly it says long distance. If I say, oh, no I want a 20
meter, instantly I have a Quad. Now INDEX and MATCH
have lots of uses. And notice in this
case, INDEX and MATCH were both looking from vertical
ranges filled with rows. Let’s go look at a
different example. Example 11 here, we
have vendors’ names. These are our suppliers. And we have lots of
different products. And so for each
product we have bids from each one of our vendors. And I need to get the low bid. And that’s easy enough. That’s not lookup. That’s hey, MIN
function, please look through these relative cell
references, Control Enter, double-click and send it down. Finding the min value is easy. But now in this cell,
I need a formula that we’ll look at the
min value and tell me which one of the vendors
or suppliers it is. If we’re doing this manually,
we would look up the 3580 within this range right here. We would find it in
the second position, and then we would go up
to the names at the top and say, hey, that’s Bay Air. And I would bring that
back to this cell. We don’t want to do it manually. And you can see that
this is a situation where we have to find a relative
position in a horizontal range, and then from this
horizontal range up here, we need to return
the vendor’s name. VLOOKUP is not going to do that. But INDEX and MATCH,
it can do this easily. Equals INDEX,
remember array– those are the values we want to go and
get and potentially bring back to the cell. These are the vendors’ names. Now because we’re
copying the formula down, and every single cell needs to
be looking at the vendor names, I need to lock this
with the F4 key, comma. Now row number, I
need MATCH function to look up the low bid
within this relative range. So right in row
number I type MATCH. Lookup value is going to be that
min value, comma, the lookup array. Relative cell references, please
always look five to my left, comma. And we definitely
need exact match here, because those numbers
are not sorted. Close parentheses. Write in the row number,
now wait a second. This is columns. So you absolutely
could go like this, and put that in column number. And it works fine. But if it’s one-way array–
that means in the INDEX array argument it’s only one
dimension– then all you have to do is put the MATCH
function in the second argument here. Close parentheses, Control
Enter, double-click and send it down. I’m going to the last
cell, and hitting F2. That is a pretty amazing
lookup situation. We have the ability
with INDEX and MATCH to look up the low bid
within all of the bids, and return the
correct vendor’s name. All right. Now, INDEX and MATCH again have
many uses in our next example. This is a two-way lookup. So down here I need to
retrieve the discount from the inside
part of this table. Now notice if I’m
doing part five, and the quantity I am buying,
that would be a 19% discount. If I change this
to part two, you could see now there’s part two. The row is the second
row in this table, and the column is the
second column in this table, and the intersecting
value is 16%. If I change this to 70
units that we purchased, then instantly the
discount of 18% is at the intersection of a row
and a column inside this lookup table. Now how are we going to do this? Well, we’re going to use INDEX. And this will be the
entire range of values that we potentially want to look
up and bring back to the cell. But we will need to determine
a row number, and a column number. Now let’s first do this
in the cell individually. We can certainly find
relative position with MATCH. Hey, I’m going to look
up part two, comma, within this vertical
range here, comma 0, because we’re doing exact match. Close parentheses. I’m going to hit Enter. It says, yeah, that’s in row 2. For the 70, notice this
is approximate match. It has to bump into the first
bigger one, and jump back. So down here, equals MATCH. I’m looking up the 70, comma,
within this range right here. MATCH type, approximate
match is the default. So I’m going to leave that out. Control Enter, and there it is. Column 3, row 2. Now we can simply look up
the discount, using INDEX. The array, this
is two-way array. And notice there is one
thing strikingly different about INDEX than VLOOKUP. We are not, for example, going
to highlight the first column where we’re trying to make
the match, or the first row up here. It’s just the actual values
that we want to potentially go get and bring back to the cell. Comma, the row number,
we already have it here. Comma, the column
number, we have it there. And that is indexed to
do a two-way lookup. When I change the
part number to 5, instantly it’s getting that 21%. Now similar to what we
have done many times before in this class, we built
this formula in multiple steps. We can put it all together. I’m going to copy this in
edit mode, Control C, escape. Come down here, Control
V. And now right where that C187 is, Control Enter. I’m going to go
and cheat and steal this right from the
cell, Control C, escape. Now I come down here. And I’m going to
click on row number to highlight that cell
reference, and Control V, Enter. Now I can come up and
steal the column match formula, Control C, escape. Come down here, double-click
that C188, Control V, and there it is. We have inside of INDEX, the
array of all of the discounts. For the row number,
we are looking up the relative position
of part number 5. For the column number,
we are looking up the relative position of
the quantity purchased. For our column number,
when I hit Enter, that is going to work. And if I change this,
18 of product 5. There is the discount, either
with the multiple steps or all in one cell. Now in INDEX and MATCH, we
saw, can do two-way lookup. It can do vertical lookup
only, or horizontal only. INDEX also has a number
of other great uses. And in example
13, another use is to look up and an entire column. So if I change this
dropdown to February, I want to look up
this entire column. Now once we look it up,
those are multiple values. We can’t display multiple
values in our cell. But we can do an aggregate
calculation, like adding. So our goal here
is to simply select from the dropdown, the
month, and instantly it gives us the total. Equals INDEX, now we’re
going to use INDEX to look up an entire column. The array, that’s the
entire two-way array, comma. And here’s the trick. We want to look up
in our case, March. So here’s March. Notice that March
involves three values. Yes, they’re in a column. But actually it’s 1, 2, 3 rows. So really when we were
looking up a column, we want all the rows. So the trick is in
row number, instead of putting a single
number like 3 or 4, we either put 0
or leave it empty. When you leave it
empty or put 0, that instructs INDEX that
you want all of the rows. So we do. We want all of the rows. Comma, now the column number. Remember we still have
columns we can select from; January, February, March, April. So INDEX needs to know
the relative position for the column number. I’m going to move
this over here. So now we do MATCH. I’m looking up the month. There’s the lookup
value, the lookup array. Here is a horizontal
range, comma. The values are not sorted. So we’re doing exact
match, close parentheses. And now we can see
inside a column number. That’s the match. That will deliver a
relative position to index. The 0 in row numbers
says, hey, get all of the rows in that column. Now I do need to come to the
end and close parentheses. And if I were to
hit Enter, it’s not going to give me what I want. Because you can’t
display multiple values. Really there’s the value
4, 12, and 2 in the cell. So when I hit F2, I can evaluate
this to prove that it actually looked up the whole column. F9, and sure
enough, there it is. Now INDEX does not deliver an
array of values like an array formula. It actually is programmed to
deliver a range of values. So that just means that we don’t
have to use some product to add or anything like that. We just put the
values that INDEX is delivering into the SUM
function, close parentheses, and Control Enter. There it is, 18. If I change this to
February, instantly I get the total for February. Now similar to looking
up an entire column, we can certainly look
up an entire row. Equals INDEX, I have to give
it the two-way array, comma, and now the row number. The row number is
going to be determined by the month we select. So in row number, we do MATCH. I’m going to say, hey, look up
January in this vertical range, comma, 0, close parentheses. So inside of row
number, there’s MATCH. Comma, and now
for column number, notice for January it is a row. So we are looking
up an entire row. But a row is always
filled with columns. There’s a 1 in that column,
a 3 in this column, a 1 in that column. So to tell INDEX to
get all of the columns, I either leave it empty, or
put a 0, close parentheses. If I evaluate this with
F9, I can see sure enough, it got January. Control Z. INDEX
delivers a range. So I simply have to
put it inside of SUM. Close parentheses,
and Control Enter. If I change this to
March, instantly I get the total for March. So INDEX and MATCH can look
up a row, look up a column, do two-way lookup
or one-way lookup. Now we want to go
onto our next example. Now our next example,
example 14, involves hey, here’s some units sold. And we need to get
the commission rate. But here’s the thing. Each product has its
own lookup table. So if the product
is ABC, in order to get the commissions
for these units, I have to use this table. If it’s EDR, I have to use
this table, EDS this table. Now there’s a few ways we can
do this, including the INDEX function second argument. But the problem with
index second argument is it only works if the
tables are on this sheet. We’re going to learn
about the CHOOSE function. And the advantage
of CHOOSE function is it can have tables
on this sheet, tables on a different sheet. They could be references
to find names. And actually CHOOSE
does a lot more. But we’re going to use CHOOSE to
look up the right table, given the product. Now here in this cell, I’m just
going to type equals CHOOSE, and look at the arguments. It needs index number. And all that means
is 1, 2, 3, 4. And you actually
put the values you want to look up right into the
function with value 1, then value 2, then value 3. If we have three tables, we
have to put the first table, ABC into value 1, and then EDR into
value 2, and EDS into value 3. That means somehow for
index number, I need a 1 for this table 2, for this
one, and 3 for this one. So, you actually have to either
have an extra helper column to indicate which table it
is, or put the VLOOKUP we’re going to use into the formula,
which we’ll see down here. You actually need
a separate table, and with our index numbers. If it finds ABC, we
want to return 1. EDR return 2, and so on. So in this table column, I’m
going to say, hey, VLOOKUP, I’m looking up ABC. There’s the lookup value, comma,
within this table right here. F4, comma, I want
to return the index number from the second
column, 2, comma. And I’m going to put 0 even
though these are sorted. They may not be sorted. So I’m going to put a 0, close
parentheses, Control Enter. Double-click, and send it down. Now we have the index number for
our CHOOSE, equals CHOOSE, tab. And there’s our index number. Comma, and now we
just put the tables. Now watch this. I’m going to highlight this. Hit F4. There it is, value 1 or
table 1, comma, value 2. There’s our lookup table 2. F4 to lock it, comma, value 3. There’s our lookup table 3. F4 to lock it. Now think about this. What did we do? We put a whole range. If I were to enter this
formula into the cell, well, it’s not going
to work at all. But we can even copy
it down, and just look. For this 3 value, if I F2
and F9 the entire CHOOSE, remember CHOOSE’s job is to
look up one of these tables. When I hit F9 I better see 0, 2. Hey, look, there’s a 0, 2%. 300, 3%; 300, 3%; all
the way to 750, 0.05. There it is. Control Z. CHOOSE is
looking up a table. Now I’m going to come up here. Now actually, CHOOSE,
the beauty of CHOOSE is you could put anything here. You could put text, numbers,
different formulas, ranges. Anything you could
have in Excel, you could put into
that argument. Now CHOOSE is going to be inside
VLOOKUP as the lookup table. So after the equal
sign, V, L, tab. And now, what am I going
to look up for VLOOKUP? Well, I’m trying to match units
in the first column of one of these. So of course, the lookup value
for VLOOKUP is units sold. There it is, comma, and boom. There’s our whole
table array which is CHOOSE to look up one of
three tables, comma at the end, and the column index number. That’s for VLOOKUP. 1, 2, the second column
has the commission rate I want to return. So for column index,
I type a 2, and we are doing approximate match. It’s got a race through and
find the first bigger one. So we’re leaving that argument
out, close parentheses, Control Enter. Double-click, and send it down. And we can look. Look at this 563 for
EDS, it races down, finds the first bigger
one, jumps back. So it better be 4%,
and sure enough, it is. Other situations where you
have multiple lookup tables, shipping tables where you
have different shipping rates. Lots of tax situations
you have multiple tables. Now what if we wanted
this all into one? Watch this. I’m going to copy this
in edit mode, Control C, and then paste it down here in
edit mode, Control V, Control Enter, and copy it down. And now I got that working. But let’s put the top
cell in edit mode. Notice that index number
is pointing to that cell. So I’m going to go and copy
in edit mode whatever’s in that cell. Now what is in that cell,
of course is VLOOKUP. That’s looking up
the product name to determine the index number. Control C, escape. Now I come down here, F2. And I just come to index number. I can double-click since it’s a
single cell reference, Control V. And there it is. That is a wild formula. Control Enter, double-click,
and send it down. So if you have a lookup
situation with multiple tables, by all means you can combine
it all together like this, or you can have VLOOKUP to look
up the table, and then VLOOKUP and choose to get
different tables. All right. Let’s look at our next example. Now we want to talk about
another function called the LOOKUP function. And look, LOOKUP doesn’t have
a V or an H. The actual equals LOOKUP function, this is
the original function, way back in 1980 in VisiCal. Long before there was
V for vertical or H for horizontal,
because LOOKUP function can do vertical or
horizontal lookup. And it can also
have a lookup value, and then they call it an
array, but it’s a table. Or watch this. You can have a lookup value,
and then a lookup vector where it matches,
and a result vector that’s completely
separate that contains the values you want to go
get, him bring back the cell. Now, the one big
drawback to LOOKUP is it only does
approximate match. Now you could actually
use this for exact match. But you would always have
to sort the first column. Now there’s two uses
for us, because most of the time we’re using
VLOOKUP INDEX and MATCH. But if we want to do
approximate match lookup, just like VLOOKUP, and we
want to enter fewer arguments because all you have to put is
the lookup value and the array. You do not have to
put the column number. You of course don’t have
to put the lookup type. Because it only does
approximate match. That’s one reason. Or the other reason
is that this function can handle array operations
without Control Shift Enter. Now let’s just look
at a very good use. Let’s say we want
to look up sales, and we want to return
the commission paid. All right. So I’m going to click on
the lookup value, and comma, this array. That means a table. It can do vertical
or horizontal, and the rule is if there
are more rows than there are columns, which there
are in this lookup table, or the rows are exactly
equal to the columns like this situation,
then it does vertical. If there are more
columns than there are rows, than it does
horizontal lookup. So in fact it’s the
shape of the array that instructs LOOKUP whether
to do vertical or horizontal. Now again, horizontal
we basically never do. It’s almost always
vertical lookup. So watch this. I have to put that in there. Now because there’s no
column index number, this LOOKUP function will
always take the value from the last column. So that’s it. That’s all I have to put if
I’m doing approximate match. And to illustrate, notice
commission is the last column. So when I hit Enter, of course,
it gives me the 250 commission. Here’s rating, and
let’s just look at how much easier this
is to enter if you’re doing an approximate match. I simply click on the value,
comma, and then the array. I’m trying to get ratings, so
I just highlight the table. And only stop at the rating. Because it will always only get
the value from the last column. And then enter. Look at that. If I change this to 1,500,
instantly everything updates. So that’s one reason,
it’s easier and faster to enter as compared to VLOOKUP. But it only does approximate. The other use for this is that
it can handle array operations without Control Shift Enter. So when you have lookup formulas
that have some array operation, and you’re doing approximate
match, we can use LOOKUP. Now here’s our situation. What if we always
wanted from this column, to find the last date, and
then return sites visited. I’m first going to create
an array calculation to look at how we get at the last date. Now these are dates. So these are numbers. So I’m going to say
equals is number. And I’m going to highlight
the entire column. So for a LOOKUP last,
we have some template. And we always want to get the
last one, close parentheses. If I highlight this,
and F9, it gives me true, true, true,
and then false. So really what I want
is the last true. Now these Boolean
values, trues and falses, are going to cause a problem. And so I want to
filter out the falses, and convert the trues to ones. So Control Z, and I’m going to
come to the front of ISNUMBER, and type a 1 divided by. Notice this is a math
operation, a math operation that will convert the trues
and falses to ones and zeros. Ah, but the zeros in the
denominator, when I hit F9, will cause a divide by 0 error. But I have numbers
that I can deal with. Because remember,
this function only does approximate match lookup. And if you give the
lookup value something bigger than any number, it
will always get the last value. Not only that, but
the LOOKUP function is programmed to
ignore these errors. So you’re not going to believe
this, Control Z. I can come, type LOOKUP, and
for the lookup value I’m going to give it some
number that’s always going to be bigger than all of the ones. Well, I’m going to put 2, comma. And there it is, a big
value that’s always bigger than any number in here, F9. The trues have been converted to
ones to get the last position. Approximate match will lookup
to race through, not find any, and so it will
pick that position. Control Z, I will come
to the end, comma. And the result vector, I get
to give it all of these values. So how this works, there’s a 2. It’s going to look
it up in this. It will find the
position of the last one. That last position will then
be used in the result vector, which will be right there. So that is how we’re going
to use LOOKUP last date to get our site visited. I come to the end, close
parentheses, Control Enter. Notice, there is the
last date, and there is the actual address. If I were to put this date right
here, instantly it changes. If I were to delete
all these, instantly it always finds the last date,
and gives me the site visited. Control Z, Z. Now if we were working solely
off of this column here, I could say equals LOOKUP. The lookup value is going to
be 2, comma, 1 divided by. And I’m assuming that we
do not have date column. I just want to find
the last one here. These are always text values. So I’m using ISTEXT. And I’m going to highlight
this whole column, close parentheses. If I look at the lookup
vector and hit F9, remember that’s an array
of numbers and errors. If I give it some big
number for a lookup value, it always races through. And if it can’t find one
bigger, it takes the last one. That will provide the position
in the lookup vector, Control Z, 4, comma, the result vector. Now I know this is kind
of a crazy formula. But if you’re
looking up last that is a lot easier than
most other types of formulas that could do this. If I had some
other address here, instantly it would give me
the last one, Control Z. Now there’s one other great
use that involves a function argument array operation. And we’re going
over to example 17. We’re still talking about
the LOOKUP function. Now before we look
at LOOKUP, let’s see if we can
solve this problem. We have a number of rolls sold. Over here we have an
approximate match table. In the first column,
0, 144, 289, and 578. And here’s the price. So the more you buy,
the more your discount. So what I’d like to do is
do a couple helper columns. And get the price,
then calculate number of rolls times
price and get revenue. And then add them all up. So this will involve
two helper columns and our final total column. Well, we can use, and
this is approximate match. So I’m using the LOOKUP. There’s the lookup value, comma. And this will be the
array, the table. And I simply highlight. Notice because I gave
it three columns, the value from the last
column will be returned. F4, and that’s it, close
parentheses, Control Enter, double-click, and send it down. Even that’s a good
trick right there. Because that’s a lot
shorter than VLOOKUP. Now we can do our revenue. Equals total number of rolls
times our price per role, Control Enter, double-click,
and send it down. Now I want to add
up, Alt equals. And I’m going to click on
the top cell, Control Shift, down arrow, Control Backspace
to jump up, and then Enter. So that’s our total. So my question is, is it
possible to skip over all this? Because we don’t care about
any of the individual prices. We don’t care about
the individual revenue. We’re only after
the overall total. Well here’s the deal. Remember this function
is kind of magic. Because it can do
array operations. What if instead of putting
just one value here, we put the whole column? Let’s try this. Equals LOOKUP, and
in lookup array, I’m just going to give it
the whole column of number of rolls. Control Shift down arrow,
Control Backspace, comma. And I want to give it a table. So I’m going to give it
its approximate match. I’m giving it the
last column that contains the thing I want to
look up, close parentheses. And you are not going
to believe this. Because I gave this
all of the items, remember a function argument
or an array operation, if I put that many items
in, it tells the function to spit out that many answers. So when I highlight
this and evaluate it, it simulates this
entire price column. So F9. and there we go. You could see 160, 160, and
198, just like we have here. Now that looks like
an array of prices. And what do I need to
do with that array? I need to multiply 168
times 540, 168 times 360, 98 times 12, and so on. That sounds like the
perfect job for multiplying this whole column times
this whole column. It sounds like a job for
SUMPRODUCT, control Z, and I’m going to put that
inside of SUMPRODUCT. There is array number 1. It’s a column with
all of our prices. I come to the end,
comma and array 2. There’s our number of rolls. Control Shift down
arrow, Control Backspace. Close parentheses, and Enter. You’ve gotta be kidding me. So that is a single cell formula
with a function argument array operation inside of LOOKUP,
doing an approximate match lookup to get the helper
column of prices times all of the number of rolls. Multiply array 2 times
array 1, and then add. That’s what SUMPRODUCT does. And Enter. All right, so for
LOOKUP, sometimes if we’re doing
lookup formulas where we need to do array operations
like this function argument array operation, or back
over here on LOOKUP. We did this crazy
lookup the last site visited based on date. Or we just plain and simple want
to do approximate match lookup, and have fewer
arguments to enter in. LOOKUP is a great function. All right. We have a couple
more LOOKUP examples. I want to go over to
sheet, example 18. Now example 18, I need to do
a simple approximate match lookup to look up the
students percentage grade. Find a match in this column, and
then return the decimal rate. The only problem is if
I do approximate match, this first column
has to be sorted from smallest to biggest. And it’s not. It’s from biggest to smallest. So what I want to do is
take this whole table, and flip it upside down. Now what I’m going
to do is I’m going to use the index to in
essence take the whole column. But I want to extract the 35th
one first, then the 34th, then the 33rd, then the 32nd. That means if I
was using index, I need a formula element
that starts at 35, 34, 33, as I copy down. Actually, we can use
the ROWS function just like we did before. And we’re going to have a
contractable range instead of an expandable range. Now that means I
need to highlight the whole first column of the
lookup table, Control Shift down arrow, Control Backspace. And what are we going to do? Earlier for an
expandable range, we locked the first number,
but not the second one. Here we’re going
to do the reverse. I’m going to lock the
48, F4 1, 2 times. And that’s it. As I copy this formula
down that will be locked. But 14 will go to 15, then
16, then 17, all the way down to D48, to
D48 which will be one which will extract the 94. So this is contracting range,
close parentheses, Control Enter, and I can copy just
down just to see that it works. And when I copy it over
to the other column, you could see that
it works just fine. Now Control Z, Z. I’m going
to put this inside of INDEX. The array I want, I want to
extract first the percentage column, then the decimal. So I’m going to Control
Shift down arrow, and then I’m going to
hit F4 once, F4 twice. Because I want only
the rows locked. Because I want only
the rows locked, but not the columns,
comma, for the array. Notice as I copy down,
it’s locked on 14 to 48. But as I copy the index
formula to this decimal column, the D’s will move to E’s,
which is exactly what I want. And in row numbers that will
give me 35, 34, 32, 33, 32, and so on. Close parentheses,
Control Enter. Oh, it’s already got the 0. Now I’m going to copy over here. It’s got the 0 there. Now I’m going to
copy it down, and I have just flipped the table. There’s the 4, there’s the 94. That is amazing. Now I can simply
use either VLOOKUP or I’m going to use
this LOOKUP again. I’m doing approximate match. There’s the lookup value
right there, comma, and this is a table. So I’m using array
1, Control Shift down arrow, F4 to lock it. And of course, it
will know always to get the decimal grade,
because that’s the last column. Look at that. Close parentheses, Control
Enter, double-click and send it down. Now this is also a
little lesson here. This person that got
94 is really mad. They’re saying, well
wait a second, 94 is a 4. Right? If you have a 94, and you’re
doing approximate match, you race to the end,
and there it is. There’s the very last one. So this should get a 4. Well of course, just
a reminder, don’t get tricked by number formatting. I’m going to expand
the decimals. And sure enough, that
wasn’t really 94. It was 93.99. All right. We have two last
examples that are related, and very important when
it comes to lookup formulas. I’m going to go over to
the sheet, example 19. Now we want to talk about a
very common task in Excel. We have two lists. And we need to compare them. For this example, we have
a master customer list, and here’s our
prospective customers. And we’ve been phoning
these customers. Now I need a formula to
tell me, hey, this customer has made it into
the master list. So I don’t need to
phone them anymore. Any ones that are not
in our master list, we’re going to
continue phoning them. Now the way you
compare two lists is to use the MATCH function. Equals MATCH, now remember
MATCH looks up an item, comma, and this is an item in list 2. The array is going to be
the entire other list. I’m going to click on the
top, Control Shift down arrow, F4 to lock it, and comma 0,
because the columns are not sorted. Now what does MATCH do? If it finds the customer in
this list, also in this this, it’ll return a
relative position. So as we copy down every
time we see a number, we know that customer
is in the master list. Control Enter, double-click,
and send it down. Wow. So 1, 2, 3, 4 made it
into the master list. The rest we need
to keep phoning. Now if we want trues
and falses, F2. MATCH delivers a number. If we’re interested in is
the item in both lists, then we use ISNUMBER. I come to the end, close
parentheses, Control Enter, double-click, and send it down. So anytime you want to
compare two lists and ask, hey, best for you, are
you in this other list? You use ISNUMBER and MATCH. Now let’s go to our last
example, example 20. This is a great library example. Here is our database of
all of the library books we should have. Here is the list
that we got when we did our inventory count. So what we’re really asking is,
is this item not over there. That means it was
in our database, but we didn’t find it when
we counted all the books. Well, any time you’re
comparing two list, you’re still using MATCH. I’m going to say, hey MATCH is
this book from our database, comma, within this
list of count control, Shift down arrow, F4, comma,
0 because they’re not sorted, close parentheses. When I Control
Enter, remember we’re after is this item not
in this list over here. Control Enter, double-click
and send it down. The N/As say, this item
is not in the other list. For us it means the
book in the database was not found when
we did our count. Since we’re interested
in the N/As, the item is not in
that other list. We don’t use ISNUMBER. We use ISNA. And we wrap it around MATCH. Close parentheses,
Control Enter, double-click, and send it down. So there is. “East
of Eden” was not found when we did
our inventory count. These two books right
here, not found. Wow. That was 20-plus amazing
examples of lookup functions and formulas. We saw how to use MATCH and
ISNA and MATCH and ISNUMBER to compare two lists. On 18, we saw how to
flip a table with INDEX and contracting range. On 17, we saw one example
plus two other examples on the lookup sheet about
the useful LOOKUP function. We talked about how to use
CHOOSE inside of VLOOKUP, to look up from multiple tables. We talked a lot about
look up columns or rows. INDEX and MATCH can
do a two-way lookup. INDEX and MATCH can
do a one-way look up when we’re looking stuff
up in a horizontal range. INDEX and MATCH can look
stuff up in a vertical range, and it can do a
type of lookup left. We talked a lot
about VLOOKUP, how to do a partial text VLOOKUP. We did a tax
example, where we had to use three VLOOKUP functions. We saw how to extract
records with ROWS and COLUMNS functions inside of VLOOKUP. We talked about this hidden
row here, right-click unhide. We talk about the
MATCH inside of VLOOKUP when we were looking up field
names for our column index number. We talked about how to
just use a column index number typed into a cell. And we started off by talking
about approximate match for commissions,
approximate match VLOOKUP for square
footage and pipe diameter. We looked at horizontal
lookup, the HLOOKUP, and we started off with
our invoice example. All right. We’ll see you next video.

10 thoughts on “Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons, 20 Examples”


  2. One of the wonderful,i have gone through a lots of videos never come across such an informative video work,thanks a lot

  3. @excellsfun Hope you can help. I have excel csv spreadsheet with 205,000 rows and 45 columns of data. Normally I have a database tool that would remove errors into a separate file. However, it's broken so I now have to do it manually. I have spreadsheet with 205000rows 45 columns and I have an error report that lists the error code and the row number that error occurs on. Can I use vlookup to remove the error from the main file (creating a clean file) and place it into a separate file (error file)? Your advice would be appreciated

Leave a Reply

Your email address will not be published. Required fields are marked *