This page shows how to use the Excel Vlookup Function when you want the closest match to your lookup value (if an exact match is not found).
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 the use of the Vlookup function with the [range_lookup] argument set to TRUE.
|$0 - $999.99||1%|
|$1,000 - $4,999.99||3%|
|$5,000 - $19,999.99||4%|
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.
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.
Bank Accounts spreadsheet with Vlookup results:
The first call to the Vlookup function, in cell C2 is:
The table below explains the arguments that are supplied to this function:
|B2||-||lookup_value is therefore the value 5.69.The|
|'Interest Rates'!A2:C5||-||lookup_value 5.69.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|
|3||-||This argument tells the function that the value to be returned should be taken from column 3 of the supplied table_array (i.e. from column C of the "Interest Rates" spreadsheet).|
|TRUE||-||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.