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

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

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


Got any excel Question?