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
I tend to use =IF(ISNA(VLOOKUP(..)),0,VLOOKUP(..)) Admittedly, I'm usually not allowing Range_Lookup = TRUE, but it would work for either case.
ReplyDeleteIt's guaranteed never to produce a "#N/A" result, but it's longer to type, less efficient to execute, and requires that the two VLOOKUP statements are kept in sync.
For the reasons you gave and to keep formulas short, I also try to avoid IF().
ReplyDelete