How to Use VLOOKUP

by Matthew Kuo on December 19, 2012

in Database Theory, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the

For most Excel users, VLOOKUP is the first complex formula they learn.  Performing a lookup is an extremely valuable function for any situation where you’re dealing with large data sets.  While the VLOOKUP function itself has become somewhat dated, as there are other better alternatives such as , it is still important to learn this function because:

  • VLOOKUP is probably the simplest lookup formula of the ones available and if you have no prior spreadsheet experience, it should be the formula you start off with
  • Despite being dated, many people still use VLOOKUP in their spreadsheets and if you ever end up auditing someone else’s spreadsheet, you’ll need to know how this formula works

Click here for a detailed explanation of why INDEX MATCH is better than VLOOKUP


Understand the Objective

The first step to learning VLOOKUP is knowing when to use it.  VLOOKUP is used for two primary purposes:

  1. To look up a value or a series of values from a large database
  2. To append a column to a table using data from a larger database

The process for using VLOOKUP doesn’t change much between the two objectives, so we’ll focus on the first objective.  In the example below, we have a value called ID circled in blue and a table of data circled in green.  The cell highlighted in yellow is the return value, where we are missing a value for State.  Essentially, we want to know which State is relevant to ID number “5”.



After understanding your objective, it’s important to learn the syntax, or inputs required, to write out the VLOOKUP formula.  The syntax for VLOOKUP has changed over time to make it simpler and easier to use.  In the latest version of Excel (2010 at the time of this writing), the syntax for a VLOOKUP automatically appears once you type in the formula and an open parenthesis.  We’ll look at each component individually.

=VLOOKUP ( lookup value table_array , col_index_num [range_lookup] )

Lookup value

The lookup value is the value we need more information on.  In the example below, this is the ID number “5”.  We will start by typing our formula in the blank cell where we want to return the missing information.  Then we select the lookup value for the first component of the syntax and follow it with a comma.


Table Array

The table array is larger data set where your return value exists.  In most cases this dataset will be large enough that you’ll want to use a formula to pull the value, rather than looking it up manually.  Once you become proficient at VLOOKUP, it also reduces the possibility of making a mistake.

To select your table array, simply click and drag around the entire data set that is relevant to your lookup formula.  And again, complete the entry of this component by inputting a comma.


Column Index Number

This component simply references which column you want to pull back your data from.  It’s important to note that the minimum number for this input is 2 – you cannot look up and return the same value  (as doing so probably doesn’t make much sense).  The maximum value depends on how large your table array is.  In the example below, we only have 5 total columns.  Therefore, our maximum input for column index number is 5.  Inputting any number higher than 5 will return an error value.

In the example below, since we want to return the State related to our ID, and City is the fourth column in the table array we selected, we input the number “4”.


Range Lookup

Few people actually know what a range lookup does, because range lookup is a functionality that few people ever use.  The range lookup simply tells your lookup formula to look for values that are close to your lookup value, not ones that are exactly the same.  The rules on what’s “close to” your lookup value are vague and if you’re working on a project where you need some level of precision, you can see why this functionality can be problematic.  You basically have three options to deal with the range lookup portion of the syntax:

1.  Choose not to use range lookup

To choose this option, input either a “0” or the word “FALSE”.  Many of us who’ve used this formula for years are used to typing in FALSE; the option to put in a 0 is relatively new.

2.  Choose to use a range lookup

To turn on the range lookup functionality, simply do the reverse of the prior inputs, and enter either “1” or “TRUE”.

3.  Ignore it

If you simply close off the parenthesis and leave out this final piece of syntax, the formula will default it to “TRUE” and have your VLOOKUP perform a range lookup.

For our particular example, because we want an exact match, we will input “0” so the VLOOKUP does not perform a range lookup.  After you’ve written your formula with your desired inputs, close the formula with a “)” and press ENTER to finish writing it.


What Excel Does

If you’ve written the formula properly, the value you were looking for will be returned in the lookup result cell.  After you’ve written all of your parameters, the formula performs a vertical lookup:

  1. Starting from the top of the leftmost column of your table array, it searches for your lookup value
  2. Once it finds your lookup value, it stops moving down and begins moving over to the right based on the column index number you specified
  3. After moving right by the specified number of columns, it returns whatever value it lands on


If your lookup result cell comes up with an error, there are a .  Over time you will learn how to troubleshoot your errors.


After you’ve mastered using VLOOKUP, you’ll be able harness one of the primary uses of Excel spreadsheets.  However, you should not stop this formula; there are other lookup formulas that are superior to VLOOKUP and provide additional functionality.  In particular, is now the preferred method to perform this type of lookup.

{ 22 comments… read them below or add one }

Gerald Herbaugh March 10, 2013 at 12:35 am

In the paragraph “Column Index Number”, there is a significant error in the following sentence: “In the example below, since we want to return the City related to our ID, and City is the fourth column in the table array we selected, we input the number “4″. In fact it is State which we want to return in the example and which is in column 4, not City.


Matthew March 10, 2013 at 12:48 am

Thanks for the catch.



yashika December 25, 2014 at 7:21 am

Thanks a lot for the Tutorial.


Joe March 12, 2013 at 8:56 am

Great tutorial! This helped a lot!


Sashankan March 13, 2013 at 5:54 am

Thanks for the Tutorial


erusa goundan April 4, 2013 at 11:54 pm

i like the vlookup


Pragya Verma April 13, 2013 at 9:54 am

very nice


Ivan May 23, 2013 at 2:15 am

Very well explained! 10x a lot!


krishna June 2, 2013 at 11:33 pm



June 7, 2013 at 8:26 am

Hello, just wanted to mention, I loved this post. It was practical. Keep on posting!


June 12, 2013 at 5:32 am



ramesh July 5, 2013 at 4:31 am

Thanks 100*time


September 5, 2013 at 6:23 pm

I have question on using excel function
1. The advance of using spreadsheet
2. The combination of column letter and the row number is:
3. What is the formula would you use to find the youngest student from a range of age?
4. True or False
– The IF function can only test for true condition
– You can create text boxes by placing text inside rectangular graphic objects
– The goal seek command can seek a specific solution to a formula
– Excel doesn’t allow you to protect you data
– You can hide your excel’s columns and rows to protect information from
being seen
5. Please clarify the difference between relative reference, absolute
reference and mixed reference
6.List 3 errors you might come across while working in Excel


David Jone November 7, 2013 at 3:45 pm

Great job Matthew! Thanks for the explaining in detail!


Gareth November 4, 2014 at 1:26 am

Just a note to say great articles on here regarding lookups but the minimum index number is 1 not 2. I often find it useful to look up the same value.


murry November 22, 2015 at 7:14 pm

Thank you!


UTTAM January 16, 2016 at 2:03 am


Your suggestion solved my problem with the #N/A issue – thanks for your help!



Matthew Kuo January 16, 2016 at 2:18 pm

Glad I could help Uttan.



abdirashid February 25, 2016 at 5:30 am

its very helpful and I am very happy to get this lesson
thank you so much


jawed subhani March 11, 2016 at 3:55 am

I learned vlookup with this post, thanks for your help!


Rach April 8, 2016 at 1:12 am

Query for col index num.

I have data in column DH, which is a lot of columns for me to count. How would i find the number in an easy way?


Deborah April 19, 2016 at 3:32 pm

Thank you for my first introduction to VLOOKUP. Easy to follow and understand. Wondering though, in the Table Array, wouldn’t you input B7 instead of B6, since B6 is a header ? Thanks for your help… I will continue to the HLOOKUP now.


Leave a Comment

{ 7 trackbacks }

Previous post:

Next post:

Related pages

excel 2010 conditional formatting not workingexcel vba isnaremoving duplicates in excel 2010how to find and delete duplicates in excelpivot a table in excelexcel duplicate recordshow to use countifs in excelblank function excelexcel formulas sheetexcel formulas not equalexcel tutorial for beginners pdfstack columns excelwhat are vlookupsuniversal studios tickets uclavlookup different workbookmaking bar charts in excelvlookup statementbin range excelduplicate rows in exceldefinition of conditional formatting in excelmulti vlookupformula for vlookup in excel 2007tick mark symbol in excelvlookup in a tablekeyboard shortcut for filter in excelsum vlookup excelexcel 2010 reference another sheetcreate a vlookup in excelwhat is the vlookup function in excelucla storeexcel vba histogramexcel vlookup formulasexcel count formulasexcel cell contains textpretty excel chartsvlookup iserrorhistogram in excelloffset in excel 2007search duplicate in excelexcel graph pointsadding excel columnsparenthesis excelcombine hlookup and vlookupdata duplicacycool excel graphsexcel modeling best practicesusing vlookupslibrary hours uclamost popular web pagesrange of cells in excel formulamicrosoft office 2010 download free trial 60 daysexcel compare two lists and return differencesfind duplicates in a column in excelchart legend excelshortcut key in excelhow to delete duplicate values in excelhow to remove duplicates from two columns in excellearn macros in excelcreate a histogram excelexcel delete duplicate valuesvba excel formulasexcel templates calendarhow to eliminate duplicate records in excelzs associates salariesduplicate excel sheetms excel histogramhow to vlookup in excel 2007iferror vlookupvlookup across multiple sheetsif function in excel with multiple conditionshow to hide a formula in excelinventory spreadsheet excelfind duplicates in excel 2007boundaries circle chartexcel linked cellsremove duplicates from exceltick symbol copy and paste