VLOOKUP Function: To perform advanced data analysis

VLOOKUP is one of the most useful functions in MS Excel. . Once you can learn to use this function effectively, you could consider yourself to be an advanced excel user.

VLOOKUP function is a great tool for data analysis and can bring back corresponding values from a big data set on the fly for multiple values. You can bring back values either from different spreadsheets or different workbooks seamlessly. VLOOKUP function can make your analysis task quite easy and quick.

VLOOKUP function searches for the value being searched in the first column of table and on finding the match, returns the value of the row from the specified column of the table corresponding to the match found.

Example 1

Using the data in TABLE 1, You wish to search for the floor number on which electrical department is situated using VLOOKUP.

 Table 1

The VLOOKUP SYNTAX, simply put, is to say

[=VLOOKUP( Lookup This Specified Value in the selected data range’s Left Most Column, Range of Cells in which the function should concentrate the search, This is the column offset number from the leftmost column from which to pick up the value that is in the same row as the lookup value, Condition to tell function to do Approximate or Exact Value Match). 

When you use this function, excel looks for this value in the first column of the table. Then, , it looks across that value’s matching row to pick the value from the column offset number specified. While doing this the function knows whether specified table is having approximate value or exact  value by looking at True and False as the last condition of this function) 

So now looking back to the above table, the formula says:

=VLOOKUP ("Electrical", B1:E10, 3, FALSE)

Using the above explanation, walking through this implies:

Find “Electrical” in the first column of table B1: E10 and if match found, return the value from 3rd column (which is floor - The 3rd column is relative to the lookup column)and also, match “Electrical” exactly and not approximately by saying False at the end.

EXAMPLE 2

Now, think of a situation, where you have to lookup not only Electrical but many department from this table, in that case, instead of giving “Electrical” as static value, you can refer to a cell in a column and make an absolute reference to the table. When copying and pasting this formula to cells, it will not change table reference. Here the formula is returning value from 4th column:

(Note: To make an absolute reference to a cell or table from relative reference, you need to put $ sign in front of referenced cell or take your curser in front of cell number in formula and press f4.)

 One more example to explain the function:

The formula and its parameters are explained below::

• The first argument to the function ‘lookup_value’ = B17 (Reference of “Mechanical”).
• Second argument ‘table_array’ = B1:E10 (Range of the table).
• Third argument ‘column_index’ = 4 (the column number whose value the VLOOKUP function will return).
• Fourth argument ‘range_lookup’ = FALSE (Signifies that look for the exact match).

   The result set:

EXAMPLE 3

Now, you may want to change the lookup array to vlookup for Head. Say, you wish to find in which room Greg S is located:

=VLOOKUP ("Greg S",C1:E10,3,FALSE)

Above formula will look up for Greg S in column C, the left most column of table selected and bring back value from column 3 which is Room # and bringing back exact match.

It is this simple. It is strongly recommended that excel users should consider using VLOOKUP function as one of the staple functions in their toolkit to achieve excellence in their analytics!!

Good Luck Advanced Excel Users!!

-Sameer Lal