MAKING EXCEL WORK FOR YOU
The VLOOKUP function performs a lookup on a table that has a vertical orientation,i.e.the data is laid out in columns. The lookup value should be housed in the first column, the one on the extreme left of the table. In these examples we will look at how VLOOKUP can be used to apply a variable discount based on the total amount
VLOOKUP takes three or four arguments.
The first argument specifies what is being looked for (500 in this example - in cell A3).
The second argument describes the location of the table being searched (G3:H7 in this case).
The third argument controls which column of the lookup table the answer is obtained from. In this example the third argument is 2. So the answer is retrieved from the second column. Column numbering is relative to the seach column (G in this case), the search column is treated as being column number 1. So in this example column 2 means the H column. The amount is 500 and the discount to be applied is found, i.e 1%.
The fourth argument of the VLOOKUP is optional. In our example the argument's value is FALSE. FALSE tells VLOOKUP to find an exact match for the item being looked for. If the item being looked for isn't found VLOOKUP will return an error. An example of this is shown next.
In the example above 1950 isn't in the search column of the lookup table (i.e it's not in column G) and a #N/A error is returned. We can specify that, if the lookup doesn't find exactly what we specified, it returns the "nearest match" it can find rather than an error. To do that we set the fourth argument to TRUE or leave it out altogether.
In the example below the amount being looked for (1150) - doesn't exist in the lookup table. VLOOKUP returns a rate of 2% - which is the discount rate for values below 2000 and above 1000. Why does the VLOOKUP choose the discount associated with 1000? VLOOKUP will always return the value associated with the lower value in such cases.
Basically, VLOOKUP assumes the search column is sorted in increasing order. VLOOKUP starts at the top of the search column and scans down through that column until it finds the first item that is greater than or equal to the item being searched for. In this example VLOOKUP will step down the search column till it finds 2000. Because VLOOKUP is designed to choose the next lower value it steps back to 1000 and that value's discount is returned.
If a "nearest match" lookup is being used and the item being searched for is larger than the last item in the search column then the last entry is returned. As in the following example.
Here the amount of 7495 is greater than the last amount in the search column - 5,000. So the tax rate for 5,000 is returned.
In a VLOOKUP if we specify a "nearest match" lookup (by omitting the fourth argument or having it TRUE) and an exact match is found, then the exact item is returned. As in the following example, an exact match for 3000 is found.
Remember, for a "nearest match" lookup to work the search column has to be sorted in ascending order. If it is not then the results will be unpredictable or wrong. Consider the following example. The search column isn't in sorted order, amounts are in random order, so the lookup fails. Based on the previous examples you might expect the formula to return 2%, however it fails because the list is unsorted.
Excel VLOOKUP Function
If the search column is not in sorted order and we are searching for a nearest match - even if an exact match exists - then the lookup may still fail. That is illustrated in the following example.
The value being searched for is in the table but isn't found because the search column isn't sorted in ascending order.
VLOOKUP assumes the search column is the leftmost column in its second argument. If you try to have the search column elsewhere the lookup will fail. As in the following example. In this example the search column is the rightmost one. The lookup has no way of knowing that you intend the right column to be the search one so it uses the leftmost one, and not surprisingly, it fails.
Another way that a VLOOKUP can fail is if the reference to the table being looked up doesn't contain enough columns. Note that in the following example the lookup table is only a single column - "G". We want to retrieve data from the adjacent column, "H", but since the second argument to the lookup doesn't contain that column the VLOOKUP fails.
Download the free VLOOKUP example workbook
© Roy Cox 2005 to 2013
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|