Excel|2007|2003|Microsoft Excel|Formula|Function|Pivto Table|Excel Topics|Passwords|Hyperlink Excel Guru

Tuesday, October 7, 2014

Use Vlookup With an Excel Spreadsheet

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.

There are many ways to use vlookup, we will explain all the most useful ways here ....

First method you can use as follows 
Understanding Vlookup

Use Vlookup With an Excel Spreadsheet Step 1 Version 3.jpg
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.


Use Vlookup With an Excel Spreadsheet Step 2 Version 3.jpg

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.


Use Vlookup With an Excel Spreadsheet Step 3 Version 3.jpg


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]


Second method you can use as follows 


Understanding the Vlookup Values

Use Vlookup With an Excel Spreadsheet Step 4 Version 2.jpg

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 you 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.
Use Vlookup With an Excel Spreadsheet Step 5 Version 2.jpg


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.
Use Vlookup With an Excel Spreadsheet Step 6 Version 2.jpg


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.
Use Vlookup With an Excel Spreadsheet Step 7 Version 2.jpg
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]