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

INDEX and MATCH. INDEX and MATCH can

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.

Thanks Mike for this video

I thought I knew how to use Vlooup function until I saw this video, It's really good, thanks Mike for sharing.

I SALUTE U FOR UR HIGH CLASS PROFESSIONAL ATTITUDE TOWARDS TEACHING N THE SKILLS U'VE GOT UR TRUELY A GENIUS N A PROFESSIONAL SKILLED TEACHER….U R FAR BEYOND EXCELLENT TEACHER NO WORDS. THANKS GOD I'VE FOUND A GENIUS SKILLED TEACHER LIKE U…UR AMAZING N BEST TEACHER OF EXCEL VERY SKILLED N LEARNED…THANKS FOR UR HONESTY N TIME.

VLookup formulas are a little difficult for me, but I learned a lot from this video.

An epic video, that requires many reviews.

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

Thanks

Why is Ref error showing, while I use index & match to extract whole row?

Mike, I watched this one even though it is older. Very epic!

@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