VLOOKUP explained in 2 minutes

Check out the video ​below for a quick explanation of the Excel Vlookup function. Or, ​if you'd rather read, the video is summarized further down the page.

play

​Video summary

The Excel function Vlookup's name comes from Vertical LOOKUP. We look up the score of a ​person in a table.

The arguments

​The first argument is the value that we're looking up. Ann in this case. Click on the cell containing Ann. The second argument is the complete table that we want to look into, except for the headers.

The third argument is the column index number, the index of the column of the value that we want to return. This means the how many-th column the value is in that we ​want to show as a result. We want to show the score, which is in the second column​, so we use 2.

The final argument is the (optional) Range lookup argument. Using false will ​make ​Excel look for the exact value, using TRUE will make it look for an approximate match. You almost always want to use FALSE ​here. Check out the Questions section below for when you ​should​ use TRUE.

Errors

If the lookup value does not exist, Excel will show a #N/A (Not Available) error. ​

More on Vlookup

​Syntax

=VLOOKUP (Lookup value, Table array, Column index number, [Range lookup])​

​Argument


​Argument description

​1

​Lookup value

​Value to search for (must be in first column of the table).

​2

​Table array

​The table to look in.

​3

​Column index number

​How many columns away the returned value is from the lookup value.

​4

​Range lookup

​Approximate (True) or precise (False).

​Another example

​Suppose you want to look up the scores of two students. You have a table containing the scores of all the students of the course on another worksheet. But you don't want to look them up yourself, you want Excel to do it for you.

Use the VLOOKUP function to search for the scores of the two students.

Excel sheet showing the two students Alice and Bob. We want to know their FOP-101 score.
The complete table of students and their scores.

​Answer

​Use =VLOOKUP(A2, ScoreSheet!A1:B19, 2, FALSE) in cell B2 to look up the score of Alice.

​Explanation

  • Lookup value A2 contains the value we are looking for: Alice.
  • Table array ScoreSheet!A1:B19 is the table in the range A1:B19 on the ScoreSheet worksheet.
  • Column index number 2 is the column in the table that contains the value we are interested in. Column 1 is the name, column 2 is the score.
  • Range lookup FALSE is because we are looking for the exact name match. Using false by default is a good rule of thumb.
  • ​Questions about Vlookup

    Why is using FALSE for the Range lookup variable a good rule of thumb?

    Because almost always, we want to look for exactly the thing we have as our reference. Looking for almost the value can lead to unexpected results. Read the answer below to see when you would want to use TRUE for this.

    When SHOULD you use TRUE for the Range lookup variable for Excel’s VLOOKUP?

    When the table you are using VLOOKUP on has values that correspond to ranges of numbers. For example:
    0 – 10 Low,
    10 – 20 Medium,
    20 – 30 High.
    If you have value 15, the result should be Medium. In this scenario, a vlookup with value 15 will return Medium if you set Range lookup to TRUE but not if it’s set to FALSE.

    What happens when the lookup value does not exist in the table?

    If the lookup value doesn’t exist, Excel will show a #N/A error in the cell where the Vlookup function is used. You can ‘catch’ this error using for example the IFERROR function.

    ​Drawbacks and how to overcome them

    ​The Vlookup function has some drawbacks:

    1. The Vlookup function will only look for the lookup value in the leftmost column of the lookup table.
      But what if you want to look up a value in the second column? You will have to reposition the table array argument to have its leftmost column one position further.
    2. The Vlookup function will only look to the right of the lookup value in the table. You will have to change the table layout if you want to look to the left of that column.

    That's it

    T​hat's all for Vlookup. Did we help you ​understand this function? Do you have other techniques to ​look up values in Excel? Let us know in the comments.

    Also, if you enjoy this type of content, you might want to look at some more ​Excel learning material.


    You may also like

    Is there a ‘minus sum’ for spreadsheets?

    7 Excel Tricks for Marketers

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

    ​Get Good at Excel on Autopilot

    Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
    Join List
    >