=The Vlookup() function
Vlookup is used to look up something in a table. There is a comparable function, hlookup, and it acts the same except across rows instead of columns. Say you are asked to manually find (to lookup) the price for an item in the following list. For instance the price of Milk. How would you go about this (pretend the list is very long.) The first thing would be to start from the top of the list and carefully go down in the first column of the list until you see Milk. Once you found it, your eyes would move to column 2 of the list (where the price is) and read that value. An important point to note here, which will be useful later, is that when you look down in the first column you're looking for an exact match. Also the first column is not necessarily sorted.
Finding an exact match with Vlookup
Enter the table beside and follow these steps to learn how you can use
Vlookup() to extract information from this table.
The cell you put the function in now displays 70. Try looking up the Qty in Stock. The only difference is that you'll need to specify 3 in the Col_index_num argument.
Finding the closest match with Vlookup
For instance, in a list of sales, you need to calculate a bonus for each salesperson. If the sales amount is less then $300, the bonus percentage is 10%. Between $300 and $400, it's 12%, between $400 and $500 it's 14%, and so on. Using multiple If() statements would be very tedious. Besides, you cannot nest more than seven If() functions. The simplest solution is to use Vlookup() to find the range in which a sales fits in. The lookup table also contains the percentage ratio associated with each sales segment.
This is how a lookup table should look like:
Note that the lookup table must be sorted in ascending order. When Vlookup() searches a match in the first column and if it doesn't find one, it finds the next largest values in the list that is not less than the value being searched. Then, it returns the entry associated with the lower boundary of the range. For instance, since 18 is greater than 10 but less than 20, the string 'Not very good' would be returned. Any value higher than 50 would have Vlookup() return 'Excellent'. A value that is less than 0 would return an error.
The following exercise will illustrate how to setup a lookup table and how to use it in a payment calculation. Take a minute to create the following small worksheet if you think it will be of use to you. It's fine if you only enter a few entries in the table on the left but you should enter the whole table on the right, which is the lookup table.
We need to calculate a bonus payment based on the amount sold. The lookup table above will determine the percentage to use in the formula. Move to C2 and follow these steps:
Since Paul sold less than $300, the bonus payment should be $0. Mike, on the other hand, sold for $310. This puts him in the $300-$400 bracket which corresponds to a 10% bonus payment. So 10% of $310 is $31.