VLOOKUP is a highly useful tool, especially when dealing with a large spreadsheet, that allows the user to quickly find specific data. VLOOKUP stands for Vertical Lookup and the name says it all. This formula searches vertically down the first column within the range listed in the formula to locate the designated search word or number. Once the desired data field is found it then moves across the row based on the second range listed in the formula. The formula then lists either a TRUE or FALSE. A TRUE value will return approximate values whereas a FALSE value will only return the exact value results. To know How to Use VLOOKUP in Excel, click here
It sounds complicated but let’s break it down a little more using the spreadsheet below.
(Two column spreadsheet, Column 1 = Item, Column 2 = Number, Items are A to H, Numbers are 1 to 8)
Say someone wants to know the Number associated with Item E. Glancing at the spreadsheet it is easy to see that the Number would be 5 but imagine a spreadsheet with over 10,000 Items in it. How would one quickly search to not only find E but to locate the Number? This is where VLOOKUP comes in extremely handy.
1. Click in any cell without data entered to type the formula.
2. The first argument you will enter will be the value you are looking for. Always remember to place a comma after each argument.
3. The second argument is the cell range in which the data is listed. For this example, the cell range is A2:B9.
4. The third argument is the number of the column where the result is entered. In this example, we are trying to find the number for E and the numbers are listed in the second column so enter a 2 as the third argument.
5. The fourth and final argument as mentioned above is whether you would like approximate matches. In this example, we only want the exact match returned so enter a FALSE as the fourth argument and close out the parenthesis and hit return and it should give you the answer of “5”.
To find the number for a different item you would simply replace the first argument with the desired search. Example: =VLOOKUP(“A”,A2:B9,2,FALSE)
To get the information about use of IF function in Excel, click at http://www.whiskydemalta.com/use-excels-function/
If the search is across multiple Sheets or Workbooks.
In this case the easiest way is to open both workbooks and begin typing your VLOOKUP formula. When you reach the second argument, the table array, click into the other sheet or workbook and select the range from there. However, you can manually enter the formula if you know the values you want entered. An example would look something like =VLOOKUP(“A”, Sheet3!A2:B17,2,FALSE)
As you can see the formula shows that “Sheet3” is now the table range. Likewise, the Name of the Workbook would be listed there if the range spanned across multiple workbooks.
So, what are a few examples of when this formula would be most useful? A Contact list is a prime example, allowing the user to quickly locate a person’s name and phone number or address. VLOOKUP would also be useful to quickly scan an inventory list and locate the item and how many are currently in stock. This is a very useful formula for many Spreadsheet uses. To read more about basic excel formulas, click here.