Graduate from Basic Excel to the Next level with the Most Crucial Function VLOOKUP!

Graduate from Basic Excel to the Next level with the Most Crucial Function – VLOOKUP!

Microsoft Excelhas some powerful tools that our life easy when searching values and analyzing data in large datasets.The “VLOOKUP function” is one such power tool that every Excel user must know. Getting hands-on with this function represents an important step in graduating from Basic Excel to next levels!!It is definitely something that you must have in your arsenal of analytical weapons when searching specific value (Lookup Value) in a large dataset. Once it identifies the specified value, it can also find and display some other piece of information that’s associated with such value and is located in the same row.

Though it sounds like the geekiest Jargon or the most complicated thing ever, it is easier to use than one thinks. Basically, “LOOKUP” is a Microsoft Excel function that searches for values in a column or row of a spreadsheet list or table. The V in VLOOKUP stands for vertical (column) i.e. It searches for Lookup value in the first column of the database, vertically.Similarly, the H in HLOOKUP stands for Horizontal i.e. it searches for Lookup value in the first row of the database, horizontally.

Check-out a simple example here: With the help of VLOOKUP we are trying to find different statistics from a database of restaurants and chefs. E.g. How many restaurants in a particular city? How many chefs in a particular city? City is the Lookup value here. The information such as number of restaurants, number of chefs lie in different columns of this database. 

Check this video out!

To use “VLOOKUP function” type =VLOOKUP in a cell

1.  Start by specifying the Lookup Value.This is the value that you wish to look up (search)! Excel will search for this value in the first column of your reference table. While searching for this value, it moves VERTICALLY downwards in the first column of the table. As a result this function is known as VLOOKUP (Vertical Lookup)!

In our example, the entire information has laid-down city-wise. The names of the cities are included in the first column. So the lookup value has to be one of those cities in the first column.

2. Then link the range of cells in the sheet or workbook from which you need to pull data (This is your Reference Database or Reference Table Array).

In our example, the entire information i.e. Number of restaurants, number of chefs and cuisinesin each city has been mentioned in the form of a table. This is the second part in your VLOOKUP formula i.e.  reference Table Array

3. In Step 3, provide the column number for the data point you’re looking for. Once the lookup value is found, you’ve got to tell Excel what information you need based on the Lookup value! So, justcount number of columns in the table, and tell Excel which column you wish to pull the data from.

In our reference table, while the number of restaurants for each city is mentioned in 3rd column, the number of chefs is mentioned in 4th column. This is the third part in your VLOOKUP formula i.e.  Column Index number

4. To end the formula, enter “0” if you wish to locate a value in the first column that EXACTLY matches with the Lookup value. Enter “1” if you wish to locate a value that APPROXIMATELY matches with the Lookup value.If you don’t enter anything here, the default value considered is 1 i.e. Approximate Match.

REMEMBER, when looking up Text values, always enter 0 here. Excel WILL NOT find an approximately matching value if you have misspelled the Lookup value.

So just ask the right question! And the genie in the form of VLOOKUP is at your service!!It lets you search for specific information in a database. Once you learn how to use VLOOKUP, you’ll be able to use it with larger and more complex databases. It helps you herd data that's scattered across different sheets and workbooks analyse this extracted information and give meaningful insights. That’s when it becomes truly useful!

For specific training on use of this function in financial calculations and models, you may consider signing up for our Specialized Program