Use Vlookup With an Excel Spreadsheet

Using Vlookup in Microsoft Excel may seem like a task easily accomplished only by professionals, but is actually a simple thing to do. Just by learning a little piece of code, you can simplify your retrieval of information from any spreadsheet.

Steps

Understanding Vlookup

  1. Know when to use Vlookup. Vlookup is a function in Excel that allows you to type the value of one cell to look up the value of a corresponding cell in the same row.
    • Use it to look up information from a large spreadsheet or if you have to find repetitive information.
    • Imagine being a teacher with a student list in Excel. You could use Vlookup to type the name of the student and immediately be given their grade from a corresponding cell.
    • Vlookup is helpful if you work in retail. You can search an item name and be given the item number or price in return.
  2. Make sure your spreadsheet is organized correctly. The “v” in Vlookup stands for “vertical.” This means that your spreadsheet must be organized in vertical lists, as the function will only search columns, not rows.
  3. Use Vlookup to find a discount. If you are using Vlookup for a business, you can format it for a table to calculate a price or discount on an item.[1]

Understanding the Vlookup Values

  1. Understand the “lookup value.” This is the cell from which you start; the place that you enter the Vlookup code.
    • This will be a cell number, such as F3. It refers to the location of your search.
    • You will enter your Vlookup code here. Whatever search you do here must be from the first column of your spreadsheet.
    • It is helpful to place this a few spaces away from the rest of your completed spreadsheet, so that you don’t mix it up with your other data.
  2. Understand the “table array.” These are the cell numbers of the complete range of data.
    • The first number will be the one on the top left corner of your spreadsheet, and the second number will be on the bottom right corner of your data.
    • Using the example of a teacher with a class list, imagine you have two columns. The first has the names of all the students, and the second has their GPA. If you have 30 students starting in A2, then the first column lists from A2-A31. The second column with grades goes from B2-B31. Therefore, the table array is A2:B31.
    • Be careful not to include your table headers. This means that you don’t include the name of each column, such as “student names,” and “GPA” in your table array. These would probably be A1 and B1 on your spreadsheet.
  3. Find the “column index number.” This is the number of the row that you are searching for data in.
    • For Vlookup to work, you must use the number of the column, not the name. So even though you are searching for your students’ GPA, you would put “2” as the column index number because the GPA is listed in the second column.
    • Don’t use the letter for this, only the number representing the column. Vlookup will not recognize “B” as the correct column, only “2.”
    • You may have to literally count the number of columns to the right to find the column index number if you are working with a very large spreadsheet.
  4. Understand the “range lookup.” This is part of the Vlookup code that wants to know if you want an exact number or an estimated one.
    • If you want an exact number, not one that is rounded or from a neighboring box, then you must put “FALSE” in your Vlookup function.
    • If you want an estimated number that is rounded or borrowed from a neighboring cell, then you can put “TRUE.”
    • If you aren’t sure which you need, it is typically safe to put “FALSE” so that you get an exact answer from your search in your spreadsheet.[2]

Using Vlookup

  1. Create your spreadsheet. You must have at least two columns of information for Vlookup to work, but you can have as many as you need
  2. In a blank cell type the Vlookup formula. In the cell, enter this formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
    • You can use any cell to write this is, but remember that whatever cell you choose is entered as the “lookup value” in your function code.
    • Refer to the guide above for the information about what each of those values should be. Following our same example of a student list using the aforementioned values, our Vlookup formula would look like this: =VLOOKUP(F3,A2:B32,2,FALSE)[1]
  3. Expand the Vlookup function to include other cells. Select the cell with your Vlookup code. In the bottom right corner, select the cell handle and drag it to include one or more other cells.
    • This allows you to search using Vlookup, because you must have at least two cells in order to have an input/output of information.
    • You can enter the purpose of each cell in an adjacent (but not joined) cell. For example, to the left of the box where you search for a student, you can put “Student Names.”
  4. Test Vlookup. To do this, enter the lookup value, as per our example it would be the name of a student, in one of the cells that you included in your Vlookup code. Then, Vlookup should automatically supply you with the grade of said student in the adjoined cell.[3]



Tips

  • Make sure you have no leading/trailing spaces or inconsistent quotation mark use in your spreadsheet.
  • 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/number of your table array. For example, our Vlookup code would be changed to =VLOOKUP(F3,$A$2:$B$32,2,FALSE)
  • If you want to explore other tools that can accomplish similar tasks to Vlookup, check out Index/Match in Excel

Related Articles

Sources and Citations

  1. ↑ 1.0 1.1 http://www.experiglot.com/2007/11/19/how-to-use-vlookup-in-excel-a-simple-tutorial-part-i/
  2. ↑ http://office.microsoft.com/en-us/excel-help/how-and-when-to-use-vlookup-RZ101862716.aspx?section=3
  3. ↑ http://www.timeatlas.com/5_minute_tips/general/learning_vlookup_in_excel#.UdBmePlBWSo
Retrieved from "https://kipkis.com/index.php?title=Use_Vlookup_With_an_Excel_Spreadsheet&oldid=364374"