27 May 2010

Avoiding #N/A in Range VLOOKUP

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