Google-sheets – Validation to check a list of values from found on a different sheet

Okay so I solved this problem with this function:

=sum(filter(J6:J25,K6:K25="Meals",month(B6:B27)=7))

This would give me the subtotal for all Meal expenses for the month of July. This is a bit of a pain to apply when referencing this data in other sheets, but it works.

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.