Excel Vlookup Tutorial Part 4 - Vlookup Example With Closest Match
This page shows how to use the Excel Vlookup Function when the [range_lookup] argument is set to TRUE (or is omitted).
When the [range_lookup] is set to TRUE, this tells the Vlookup function that, if an exact match to the lookup_value is not found, then the closest match below the lookup_value should be returned instead.
It is important to remember that, when using this option, the left-hand column of the table_array must be in ascending order. If it isn't, the results from the Vlookup function may be unpredictable.
Vlookup Closest Match Example
| Balance | Interest Rate |
|---|---|
| $0 - $999.99 | 1% |
| $1,000 - $4,999.99 | 3% |
| $5,000 - $19,999.99 | 4% |
| Over $20,000 | 5% |
Imagine a bank account has a variable interest rate that depends upon its balance, as shown in the table on the rightabove table. These interest rate categories are stored in the "Interest Rate" spreadsheet below.
The "Bank Accounts" spreadsheet below shows several bank accounts, along with their current balances.
|
Interest Rates:
|
Bank Accounts:
|
Column C of the "Bank Accounts" spreadsheet is currently blank, but this column needs to be updated with the interest rate that is to be applied to each bank account, depending on the account's current balance. This can be done using the Vlookup function, as shown below:
|
Bank Accounts spreadsheet with Vlookup functions:
|
For each of the bank accounts in the above spreadsheet, the Vlookup function finds the closest value below the current balance, in column A of the "Interest Rates" spreadsheet. The function then returns the corresponding value from column C of this spreadsheet.
The results of the Vlookup functions are shown below:
|
Bank Accounts spreadsheet with Vlookup results:
|
Vlookup Example Explained
The first call to the Vlookup function, in cell C2 is:
=VLOOKUP( B2, 'Interest Rates'!A2:C5, 3, TRUE )
The table below explains the arguments that are supplied to this function:
| B2 | - |
Cell B2 is the lookup_value (i.e. the value to be searched for). The lookup_value is therefore the value 5.69. |
| 'Interest Rates'!A2:C5 | - |
The table_array is the range A2-C5 of the "Interest Rates" spreadsheet. The Vlookup function will search down the left column (i.e. column A) of this array, when searching for the closest match equal to or below the lookup_value 5.69. |
| 3 | - | The col_index_num argument is set to the value 3, meaning that the value to be returned by the Vlookup function should be taken from column 3 of the supplied table_array (i.e. from column C of the "Interest Rates" spreadsheet). |
| TRUE | - |
The [range_lookup] argument is set to TRUE, which tells the Vlookup function to find the closest match to the lookup_value. I.e. if an exact match is not found, then the function should return the closest value below the lookup_value. Note that this value could have been omitted from the above formula as, by default, it uses the value TRUE. |
For further Vlookup examples, see the Microsoft Office website.