How To Use VLOOKUP in Excel

  • MORE

Microsoft Excel 2013 boasts a huge number of handy functions and utilities, many of which go unused by the average user. If you find yourself frequently needing to consult a table to find the same data, however, you'll probably want to learn how to use VLOOKUP. Short for "vertical lookup," VLOOKUP takes advantage of vertically-aligned tables to quickly find data associated with a value the user enters.

Note that, though this tutorial was written for Excel 2013, it works the same way in the latest version of Excel.

If you know the name of a product, for instance, and you want to quickly determine its price, you can simply enter the product name into Excel and VLOOKUP will find the price for you. To the novice Excel user, however, setting up VLOOKUP can look like an intimidating process -- but it needn't be. Just follow our step-by-step tutorial to start using VLOOKUP today. 

  1. Click the cell where you want the VLOOKUP formula to be calculated.

    how to vlookup 1 675403

  2. Click "Formula" at the top of the screen.

    how to vlookup 2 675403

  3. Click "Lookup & Reference" on the Ribbon.

    how to vlookup 3 675403

  4. Click "VLOOKUP" at the bottom of the drop-down menu.

    how to vlookup 4 675403

  5. Specify the cell in which you will enter the value whose data you're looking for. In this case, our lookup value is H2, since this is where we will input the name of a tournament such as "PGA Championship," so we input "H2" in the lookup_value box of the popup window. Once we've set up VLOOKUP properly, Excel will return the tournament's Total Rating Value in cell H3 when we type the tournament name in cell H2.

    how to vlookup 5 675403

  6. Specify the data which you want VLOOKUP to use for its search in the table_array box. In this case, we've selected the entire table (excluding the headers).

    how to vlookup 6 675403

  7. Specify the column number which VLOOKUP will use to find the relevant data in the col_index_num box. Somewhat confusingly, VLOOKUP requires you to use the numerical value of the of the column rather than its letter value. In this case, we want VLOOKUP to use the Total Rating Value column -- column D -- so we enter the number 4.

    how to vlookup 7 675403

  8. Specify whether you need an exact match by entering either FALSE (exact match) or TRUE (approximate match) in the range_lookup box. In this case, we want an exact match so we enter FALSE.

    how to vlookup 8 675403

  9. Click "OK" at the bottom of the popup window.

    how to vlookup 9 675403

  10. Enter the value whose data you're searching for. In our example, we want to find the Total Value Rating of the PGA Championship, so we type "PGA Championship" into cell H2 and VLOOKUP automatically produces the Total Value Rating (in this case, 914) in cell H3.

    how to vlookup 10 675403

Using VLOOKUP, you can not only search for individual values, but also combine two worksheets into one. For example, if you have one worksheet with names and phone numbers and another sheet with names and email addresses, you can put the email addresses next to the names and phone numbers by using VLOOKUP. 

Add a comment
  • Aman Bharat Says:

    Nice info and enough input there to learn VLookup

  • Aly Maamoun Says:

    Absolutely and utterly useless.

  • _Raza Says:

    In case anyone has the same problem, the lookup value must be found in the first column. For example, the Events column in the above must always be in column 1 if "PGA Championship" is used. I can't seem to find a workaround apart from moving the entire column.

  • joene Says:

    dont get it? so confusing. i wish there was a video to explain it

  • nkosinathi simphiwe Says:

    I need to know how to calculate the lookup function tool in excel

  • shrikant parikh Says:

    rubbish, not required for online selling

  • S c Rana Says:

    Student Marksheet nambar order use vlookup formula for secondary school marksheet

  • Susan Brack Says:

    Thank you, clear and concise worked first time and very helpful.

  • Daryl Says:

    Not sure about Excel 2013 but I recall Vlookup is fussy about whether the data is text and you can't just use Format Cells, you have to click on the little exclamation mark and use that to convert cells.

  • Phway Phway Says:

    I'm learning all these lesson.

  • Alex1 Says:

    I am grateful as I now have some understanding of vlookup. Though I agree with comments below on some missing steps and the problem with merged cells. My particular problem means that I need to compare on set of data with another in different spreadsheet - I am guessing I will need to copy this over prior to writing the vl command? A silly question perhaps if you know a lot about Excel but.....
    And how do I turn the data into a table. Jeez, I am a remedial case, I know!
    Thanks for your help.

  • x81234567 Says:

    Great tutorial. Thanks for the help.

  • Eyeball Says:

    I am lost at step 6. Where did Table2 come from? How did you get that value there? Totally confused on how you got that in there. Help!

  • Pam Says:

    Thank you for posting!

    This was very helpful!

  • Katt Says:

    You did not mention that when the data table that you are working with contains merged cells you must use the number of the 1st cell of the merged cells as if the merge did not exist. Example: if you have 3 merged cells in your table you are not going to use 1,2,3 to access the data. You will use 1, 4, 10 depending on the size of each of the merged cells and at what numerical location each one starts in the table.

  • Saffron Says:

    phweew! Thank you, I knew it had to be easy.. just needed to turn it into a table! Thank you for this article! Saving me TONS of time!

  • john Says:

    This site is useless.

  • Excel Training Courses Says:

    VLOOKUP is an extremely useful tool, and learning how to use it is easier than you think!
    Great post! Thanks a lot for sharing the useful information.

Back to top