I was using VLOOKUP to put percentages in buckets. Here's the lookup table:
a b 1 0 1 2 25 2 3 50 3 4 75 4
And here's the formula I added to my data sheet:
=VLOOKUP(input, A1:B4, 2, TRUE). When the fourth parameter, Range_Lookup is set to TRUE, the function will match a range instead of an exact input value. If the input value was greater than 0 (e.g. 0, 25.5), VLOOKUP returns a bucket number, as expected. My input data was dirty and included negative numbers (e.g. -0.5, -1), so VLOOKUP would then return
#N/A and dependant formulas would show an error.
One solution was enclose the input value in an IF function and avoid negative input values:
=VLOOKUP(IF(input < 0, 0, input), A1:B4, 2, TRUE).
After reading about VLOOKUP, Range_Lookup parameter ...
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
... another way to avoid the
#N/A error is to provide a dummy value smaller than all the negative values and an extra bucket:
a b 1 -99 0 2 0 1 3 25 2 4 50 3 5 75 4