Microsoft Office Tutorials

Friday, November 10, 2017

Try it! Transcript



An explanation of each of the function's arguments.

H2 is the cell that contains the lookup value.
B3:F25 contains the lookup values (column B) and the corresponding data you want to find, always to the right of column B.
3 is the column that you think contains the data you want to find. It is the third column over from B.
FALSE means you want an exact match.

The previous video showed you one way to use the VLOOKUP function.

Now, let's break that example down and see how it works.

VLOOKUP uses four arguments, or pieces of data.

The first argument is called the lookup value, and it is the data you know.

If I were using a phone book, the lookup value would be someone's name.

In this example though, the lookup values are Part Numbers.

And I'm using a cell reference as a placeholder for those Part Numbers.

If you wanted to, you could also enter the values directly in the formula, like so.

But, as a rule, it is easier to enter values in cells than in formulas, so that is what I did here.

The next argument is the block of values that you want to search. Excel calls this the table array or the lookup table.

And our example uses cells B3 through E52.

You can use any cell range that you think will return the data you need to find, but you need to remember a gotcha.

The lookup values, the data you know, have to be in the left-hand column of your lookup table, your cell range.

You can have data to the left of your lookup values, but VLOOKUP won't search there.

The next argument, the number 3, is a column reference.

It tells VLOOKUP where you expect to find the data you want to see.

We are using column D, the third column over from the lookup values, so I entered 3.

And this also points to another gotcha: Your data has to be arranged in columns, vertically.

Just remember that the V in VLOOKUP stands for Vertical, and columns are vertical.

The last argument is called the range lookup, and it tells the function that I want an exact match, or a partial match to my lookup value.

I am using FALSE to give me exact matches. I entered a Part Number, I got a Price.

But sometimes, you only want a partial match.

For example, this Excel table calculates discounts, and it assumes customers don't want to buy exactly ten or a hundred of anything.

In other words, I don't want to limit them to finding matches to just the values in this column.

So, I enter the number of items purchased, and I get a Discount of nine percent.

If you look at the arguments for the function, you can see it uses TRUE instead of FALSE.

That lets me enter Quantities that don't match the values listed in the table.

So, another gotcha to remember: 'False' returns exact matches, 'True' returns partial matches.

Also, if you want to use TRUE, you can just leave the argument blank.

So that's how VLOOKUP works.

And next, I'll show you how to use it to find data on a different worksheet.

Friday, November 10, 2017