Excel Vlookup Tutorial Part 4 - Vlookup Example With Closest Match

If the [range_lookup] argument in the Vlookup function is set to TRUE (or is omitted), this tells the Vlookup function that if it is unable to find an exact match to the lookup_value, then the closest match below the lookup_value should be used instead.

It is important to remember that, when the [range_lookup] argument is set to TRUE, 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.

The following example shows a Vlookup function with the [range_lookup] argument set to TRUE.

Vlookup 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 right.

The "Interest Rate" spreadsheet below shows these interest rate categories and the "Bank Accounts" spreadsheet below shows several bank accounts, along with their current balances.

 Interest Rates:
Spreadsheet of Interest Rates Used in Excel Vlookup Example
 Bank Accounts:
Spreadsheet of Bank Accounts Used in Excel Vlookup Example

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:
Spreadsheet of Bank Accounts with Vlookup Formulas

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:
Spreadsheet of Bank Accounts with Vlookup Function Results

Vlookup Example Explained

The first call to the Vlookup function, in C2 is:

=VLOOKUP( B2, 'Interest Rates'!A2:C5, 3, TRUE )

The table below explains the arguments that are supplied to this function:

A2 - Sets the lookup_value (i.e. the value to be searched for) to be the contents of cell B2
The lookup_value is, therefore, the value 5.69
'Interest Rates'!A2:C5 - Sets the lookup_array to be the range A2-C5 of the "Interest Rates" spreadsheet.
The Vlookup function will search down the leftmost column (i.e. column A) of this array, when searching for the closest match below the lookup_value 5.69
3 - Tells the function that the value to be returned should be taken from column 3 of the supplied array (i.e. from column C of the "Interest Rates" spreadsheet).
TRUE - Tells the Vlookup function that we require a closest match to the lookup_value.
I.e. if an exact match is not found, then use the closest value below the lookup_value.

Note that this value could have been omitted from the above formula as, by default, it takes on the value TRUE.

Further Vlookup examples are provided on the Microsoft Office website.

Go To Excel Vlookup Tutorial Part 5 - Fix Your Vlookup Error

Return to the Excel Vlookup Tutorial Page