How to Use VLOOKUP with an Excel Spreadsheet

PDF download Download Article
A beginner's guide to the VLOOKUP function in Microsoft Excel
PDF download Download Article

This wikiHow teaches you how to find a cell's corresponding information in Microsoft Excel using the VLOOKUP formula. The VLOOKUP formula is useful for finding things like employee salary values or your budget on a given date. You can use VLOOKUP in both Windows and Mac versions of Excel.

Steps

PDF download Download Article
  1. Double-click the Excel document that contains the data for which you want to use the VLOOKUP function.
    • If you haven't yet created your document, open Excel, click Blank workbook (Windows only), and enter your data by column.
  2. VLOOKUP only works with data that's organized in columns (e.g., in vertical sections), meaning that your data most likely has headers in the top row of cells but not in the far-left column of cells.
    • If your data is organized by rows instead, you'll won't be able to use VLOOKUP to find a value.
    Advertisement
  3. The VLOOKUP formula consists of four main parts, each of which refers to a piece of information in your spreadsheet: [1]
    • Lookup Value - The cell across from which the data that you want to look up is in. For example, if you want to look up data in cell F3 , your lookup value will be in the third row of the spreadsheet.
    • Table Array - Your entire table's span from the top-left cell to the bottom-right cell (not including headers). For example, if your table starts at A2 , goes down to A20 , and extends over to the F column, your table goes from A2 to F20 .
    • Column Index Number - The index number of the column in which the value that you want to look up is in. A column's "index number" refers to its order number; for example, in a spreadsheet that has data in columns A , B , and C , the index number of A would be 1, B would be 2, and C would be 3. The index number starts at 1 for the far-left column of data, so if your data starts in column F , its index number is 1.
    • Range Lookup - You'll normally want a precise answer for the VLOOKUP result, which can be achieved by typing in FALSE for this value. For an estimate, you can type in TRUE instead.
  4. Click a cell in which you want to store the result of the VLOOKUP formula.
  5. Type in =VLOOKUP( to start the VLOOKUP formula. The rest of the formula will go between the open parenthesis and a closed one at the end.
  6. Find the cell in which the lookup value is written, then enter the cell's name into the VLOOKUP formula followed by a comma. [2]
    • For example, if the lookup value is written in cell A12 , you'd type A12, into the formula.
    • You'll separate each section of the formula with a comma, but you don't need to use spaces.
  7. Find the top-left cell in which data is stored and enter its name into the formula, type a colon ( : ), find the bottom-right cell in the data group and add it to the formula, and then type a comma.
    • For example, if your table goes from cell A2 to cell C20 , you'd type A2:C20, into the VLOOKUP formula.
  8. Find the index number of the column that contains the value that you want VLOOKUP to display, then type it into the formula with a comma after it.
    • For example, if your table uses the A , B , and C columns and the data that you want is in C , you would type in 3, here.
  9. This will allow VLOOKUP to find the exact value in your specified column for the selected item. Your formula should look something like this:
    • =VLOOKUP(A12,A2:C20,3,FALSE)
  10. Doing so will run your formula and display the result in the formula's cell.
    11. Advertisement

Community Q&A

Search
Add New Question
  • Question
    Can I put Vlookup formula for 5 raw data in one command?
    NurtureTech Academy
    Community Answer
    Yes, you can get 5 raw data on a single command with array function. For example; Vlookup (A1,A1:F5,{2,3,4,5,6},0) after than do not hit enter you have to press ctrl+shift+enter.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 15 Helpful 32
  • Question
    How do I find repetitive information?
    Community Answer
    Use Conditional Formatting under the Home Tab in Excel. You can highlight duplicates. It is also possible to downright remove duplicates using another function under the Data Tab
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 10 Helpful 26
  • Question
    What is excel coding?
    Community Answer
    Excel uses VBA. To access this, you need to edit your ribbon banner and select the "Developer" option to be added to your ribbon. This is typically used for things like Macros.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 6 Helpful 10
See more answers
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Read Video Transcript

      Tips

      • To keep the cell value from changing in your VLOOKUP code when you add or adjust cells in your table, put a '$' before each letter and number of each cell name. For example, A12 would become $A$12 , and A2:C20 would become $A$2:$C$20 .
        Thanks
        Helpful 3 Not Helpful 0
      • A common application of VLOOKUP for an inventory sheet entails plugging an item's name into the "lookup value" section and using the item's cost column as the "column index number" value.
        Thanks
        Helpful 1 Not Helpful 1
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      • Double-check your column index number before plugging it into the VLOOKUP formula, as the column index number changes depending on which column your data starts in—not the order of the columns themselves.
        Thanks
        Helpful 0 Not Helpful 0
      Advertisement

      You Might Also Like

      5 Easy Steps to Unmerge Cells in Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Compare Data in Excel
      Create a Gradebook on Microsoft Excel: Make a Weighted Points Grade Sheet
      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      Advertisement

      References

      1. ↑ https://spreadsheeto.com/vlookup/
      2. ↑ https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

      About This Article

      Article Summary X

      When you need to search for certain information in a range of cells, you can write a formula using the VLOOKUP function. To use VLOOKUP, you'll need to know what you want to look up, the range of data to search, which column contains the data you're looking for, and whether the answer should be exact or estimated. To start your VLOOKUP formula, click an empty cell, and then type an equals sign, followed by VLOOKUP. Now, type a left parenthesis, followed by the range of cells you want to search separated by a colon. For example if your data starts with A2 and ends with E10, you'll enter A2:E10. Type a comma immediately after the range, followed by the index number of the column containing the answer you're looking for. The index number is based on the column letter—column A is 1, B is 2, C is 3, and so on. Immediately after the index number, type another comma, and then the word FALSE to indicate that you want to see exact matches. End the formula by typing a right-parenthesis, and then press the Enter or Return key to see if the formula finds the data in the range you selected.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 607,252 times.

      Reader Success Stories

      • Anonymous

        Feb 10, 2019

        "It is very useful to us for auditing as well as stock keeping."
        Rated this article:
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement