## =The Vlookup() functionVlookup 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
Enter the table beside and follow these steps to learn how you can use
Vlookup() to extract information from this table. - The
**Lookup_value**argument is the entry you're looking for in the first column of a list. - The
**Table_array**argument is the list containing the item sough as well as the value you want to return. You can safely select the complete table A2:C10. - The
**Col_index_num**(Column index number) is the relative column number in the list. Nothing to do with where it is in Excel, it's the column number*in the table*. We want to retrieve the price, so type 2. The price is in the second column of the table. - The
**Range_lookup**argument is critical. Read its definition at the bottom of the Formula Palette. It says that you can use Vlookup to find a 'closest match' or an 'exact match'. To have Vlookup find an exact match, you need to specify False in the fourth argument. You can also use 0. Type False or 0 and click**OK**.
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
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: - After pressing = move to the left and type the multiplication character. Your formula should be =B2*.
- At the far left of the Formula Bar open the drop-down list. If
VLOOKUP is there, select it otherwise choose
**More functions**and find Vlookup in the**Lookup & Reference**category. - In the
**Lookup_value**text box, select B2. - In the
**Table_array**text box, select E2:F7. Hit F4 twice to lock the row number because the formula will be copied down. - In the
**Col_index_num**text box, type 2. - In the
**Range_lookup**text box, type 1 or True. Read the definition of this argument in the bottom section of the Formula Palette - Click
**OK**and copy the formula down and format as currency.
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. | ||||||||||||||||||||||||