=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

Vlookup searches the first column of a list until it finds a match, then it returns the value associated with that item in the specified column. In the table on the right, you would use Vlookup() to lookup the price or the qty in stock for an item  you're searching. The exact name of the item is mandatory.

Enter the table beside and follow these steps to learn how you can use Vlookup() to extract information from this table.

The syntax for =vlookup is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

• 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 Col_index_num argument.

Finding the closest match with Vlookup

Another interesting use of Vlookup() and Hlookup() is to find in what segment or bracket a value fits in a table. You can use this to, for example, find in which tax bracket a salary is in. Another good use of this is to lookup the grade letter a student's grade belongs to. You simply need to have the rank table available somewhere  in the workbook and Vlookup will find the closest match that is not less than the value in the ranking table.

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.