Ms-Excel world is amazing in itself. I believe we spend a lot of our life's precious time dealing with computers, laptops, I-Pad and various other gazettes. We spend a lot of time dealing with ms-office or similar products. Ms-Excel world in the form of rows and columns offers, so many functionalities that sometime we realized that there is a lot to learn about these basic software’s.
Ms-Excel in its kitty has a rich library of functions and formulas which helps in our daily routines. Recently I came to know about one of superman function of Ms-Excel “vLookup”.
vLookup() stands for Vertical Lookup. This function searches for values vertically that is in a column row after row.
What does the vLookup() function do?
vLookup() function searches for a value in a RANGE/list/column(s) and returns the corresponding value from the right side columns. That is it gives you the facility to search for a string or value in a list and you can specify what values from the right side columns you want to return. This will be clear from an example. Let us say we have data like this in our excel sheet.
Using vLookup() we can search of any employee about his Age, Dept or Salary. Our RANGE/list in this case will be all columns (Empname, age, dept and salary) and all rows. We can specify any employeeName in the search criteria of vLookup() and return any columns(age, dept or salary) from the right side . The vLookup() will search in the very first column i.e. in EmpName. For example if I have to return salary of “Vijay” the vLookup() function will take following parameter:
=VLOOKUP("Vijay",B3:E6,4)
Wild card searches with vLookup
One of the interesting fact about vLookup() function is that it gives the flexibility to search with wild card character. If you are not sure about the search value, you can use the wild card character(*,?) to search with. That is if you want to search in above shown data for salary of employee, starting with “S” character you can write the vLookup formula like this:
=VLOOKUP("S*",B3:E6,4,FALSE)
You can apply the “*” (represent any character) and “?” (represent one character) wild card character to search for values in Range/colum(s). So if you want to search for employee salary having only three character in his name, vLookup() will work for you as following:
=VLOOKUP("???",B2:E6,4,FALSE)
This will return the salary of “Jay”, whose name has only 3 characters.
Limitation of vLookup function.
One of the limitation of vLookup() function is that it cannot search for columns in it right side. That is it always returns the values from his left side of column.
Although, this limitation can be overlooked by placing the search column as very first column in our list or range.
Once you know and be familiar with the use of vLookup(), you will be amazed to see how this superman function when club with the other Ms-Excel function works like a charm.
