Vlookup Example with Closest Match

This page shows the way to use the excel Vlookup function once the [range_lookup] argument is about to TRUE (or is omitted).

When the [range_lookup] is about to TRUE, this tells the Vlookup function that, if a precise match to the lookup_value isn’t found, then the nearest match below the lookup_value ought to be returned instead.

It is necessary to remember that, once using this feature, the left-hand column of the table_array should be in ascending order. If it isn’t, the results from the Vlookup function could also be unpredictable.

Vlookup Closest Match Example

Imagine a bank account has a variable rate of interest that depends upon its balance, as shown within the table on below. This rate of interest categories are keep within the “Interest Rate” workbook below.

The “Bank Accounts” workbook below shows many bank accounts, together with their current balances.

Column C of the “Bank Accounts” workbook is presently blank; however this column has to be updated with the rate of interest that’s to be applied to every bank account, counting on the account’s current balance. This will be done using the Vlookup function, as shown below:

For each of the bank accounts within the above workbook, the Vlookup function finds the nearest price below this balance, in column A of the “Interest Rates” workbook. The function then returns the corresponding value from column C of this workbook.

The results of the Vlookup functions are shown below:


Please Share