Results 1 to 3 of 3
2012-02-01, 06:11 #1
- Join Date
- Oct 2002
- Mornington Peninsula
- Thanked 0 Times in 0 Posts
Vlookup of negative numbers - keep getting N/A# error
Am doing a vlookup where the lookup_value is a negative number. My table_array has some negatives in columns in sequence through to some positive values.
I have sorted column one into ascending order, with the greatest negative in row 1 down to zero then further down to the lowest positive number. I assume that follows the rule of first column sorted in ascending order. I haven't bothered to use the 'false' parameter because of the sorting.
In the attached file, I am looking for the "bonus" payable from my table, based on the % in cell B3 - which is negative in this example, but can be positive in the real file.
My lookup formula keeps showing an N/A# error. If I put the ABS function around the formula in B3 to show a positive number, the vlookup formula shows a result, but of course this is inaccurate as it then returns the value from a positive figure in column 1 of the table_array.
Help please?Many thanks for any help, much appreciated.
Have a great day!
2012-02-01, 06:55 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,471 Times in 1,339 Posts
You don't state what version of Excel you are using. From the .xlsx extension it is 2007 or 2010. I'm using 2010 and your formula appears to be working correctly. What you may have forgotten that a number showing as -7% is really -0.07 as a regular number as represented in your lookup table (see below).
2012-02-01, 10:44 #3
- Join Date
- Mar 2002
- Newcazzle, UK
- Thanked 510 Times in 486 Posts
Further to RG's answer, you could always format your lookup table to display as percents with as many decimal places as you like if you prefer to work with all % values rather than a mixture of %lookup and decimal table values.
You will then see clearly that any lookup values which are 'greater' than negative 30% e.g. -35%, -40% -56.7% etc will fall 'outside' the lower limit of -30% and will therefore return a #N/A value.
You can fix this by extending your lookup range to include a zero bonus rate for say -100%, or -250% or whatever.
Of course, any lookup values greater than the highest positive value in your lookup table (currently 1% in your example file) will always return the associated bonus value (currently 12 in your example)