Google-sheets – Searching a value between two columns on a reference sheet

google sheetsvlookup

I have my main sheet that looks like this:

Main Sheet

The Compromise column (Column F) bases its value on another sheet. To compute its value, we check if the value of the Basic Tax (Column B) falls equal or between the first two columns of the reference sheet:

Reference Sheet

I can actually perform a simple VLOOKUP() function, but how can I implement that referencing to a range instead of a singe column?

How can I derive the value for the Compromise column?

Best Answer

vlookup is exactly the right tool here. With is_sorted set to TRUE, it finds the largest number that is less than or equal to the search key. Specifically, the formula 

=vlookup(B2, ReferenceSheet!A$4:C, 3, True)

returns the 3rd column entry of the range (ReferenceSheet!A$4:C) namely the value in C column.

The only issue here is the equality case. If the search key is exactly 500.00, then vlookup will match row 5 and return 400, which is not what you want. (This would not be a problem if the lower end of every range was inclusive, which is often the case in tax tables).

There are two solutions for the equality case issue:

  1. Subtract 0.01 (or 0.0000001) from the basic tax value prior to comparison, to bump the borderline case to the previous bracket. That is, do =vlookup(B2-0.01, ReferenceSheet!A$4:C, 3, True)
  2. Change the tax table so that the lower end of every range is inclusive.
Related Topic