This function turns Excel in to a database management system. There are plenty of databases out there such as Access, SQL Server, MySQL, Oracle etc, but the power Excel user always finds a way to turn Excel in to a functioning Database.
Excel has its limitations when it comes to storing large amounts of data. I would recommend anyone to consider deploying one of the above mentioned databases if they have large amounts of data. The inability of Excel to separate the storage of information from the presentation of it is its key drawback.
Having said that, I want to move on to show you how cool the VLOOKUP() function is.
Consider the following spreadsheet. Each row is a record containing information about a bunch of university students.
VLOOKUP() Excel Function
This spreadsheet only has a few records. Most databases have hundreds if not thousands of records. Now imagine for a moment that you would like to isolate the information of a student based on his or her student number.
The assumption here is that the student number is unique to each student – no two students have the same student number. Having a unique value for each record is crucial for the success of a database and in our case, the VLOOKUP() function.
Let’s retrieve some information based on the student number 350-5342
VLOOKUP(Lookup value, Lookup Array, column Index Number, Range lookup)
VLOOKUP(350-5342, A:H, 2, 0)
The above formula tells excel to search for the value 350-5342 in the leftmost column in the columns ranging from A to H (the array)and pick the value in the second column in the array – column B (first name).
The result is Jason.
The second column happens to be first name because the array ranges from column A to H. “A” being column 1 and “B” being column 2.
The function always searches the leftmost column only, so make sure the criteria you are searching for (student number) is the first column you select and that it is the leftmost column in your array.
The formula stops immediately after it finds a match, therefore by some twisted act of fate if two students have the same student number, only one will be recognized.
Based on the above spreadsheet, the formula will result in the following values. Notice that I changed only the column index value to 2, 3 and 6.
VLOOKUP(350-5342, A:H, 2, 0) = Jason
VLOOKUP(350-5342, A:H, 3, 0) = Zerter
VLOOKUP(350-5342, A:H, 6, 0) = $2,587.00
I will not talk about the Range Lookup criteria because I have never used it and am not sure how it can be useful.
Related posts:
It sounds like you’re creating problems yourself by trying to solve this issue instead of looking at why their is a problem in the first place
There is no problem. This was an example I made up to explain how the VLOOKUP function in Excel works. The Vlookup is an easy function that many seasoned excel users take for granted. its amazing how many users are unaware of this tool. My only hope is that this article can help educate them and help them save time.