Google-sheets – Insert value after analysing 2 cells

Try this formula for cell H2:

=JOIN(", ", FILTER(B$1:E$1, B2:E2 = 0))

This filters the header row (B1:E1) on values from B2:E2 which has the value 0. The resulting strings are joined with ,.

You can copy this formula to the other cells in column H by dragging it across. The B2:E2 will adjust automatically to match the other rows.

Feel free to look at and copy the example spreadsheet I've set up.

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.