Related topics
×
VLOOKUP — When and how to use it
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
VLOOKUP — When and how to use it

The nuts and bolts of VLOOKUP

Important: Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor. This feature is only available if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

An explanation of each of the function's arguments.

The 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.

Want more?

VLOOKUP function

Quick reference card: VLOOKUP refresher

Quick reference card: VLOOKUP troubleshooting tips

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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×