excel Using VLOOKUP to work out bonus percent (example with the "default" behaviour)


Example

In most cases, the range_lookup is used as FALSE (an exact match). The default for this parameter is TRUE - it is less commonly used in this form, but this example shows one usecase. A supermarket gives a bonus based on the customers monthly spend.

enter image description here

If the customer spends 250 EUR or more in a month, they get 1% bonus; 500 EUR or more gives 2.5% etc. Of course, the customer won't always spend exactly one of the values in the table!

=VLOOKUP(261,$A$2:$B$6,2,TRUE)

In this mode, the VLOOKUP will find the first value in column A (following bottom-up steps) less than or equal to the value 261 -> i.e. we will get the 1% value returned. For this non-exact match, the table must be sorted in ascending order of the first column.

  • The value you're retrieving data for is 261
  • The table you're searching is located in the range $A$2:$B$6
  • The column you want to return data from is the 2nd column from the left
  • You only want to return results where there is an non-exact match (TRUE) we could leave off this TRUE as it is the default