Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Mornington Peninsula
    Thanked 0 Times in 0 Posts

    Vlookup of negative numbers - keep getting N/A# error

    Hi all,

    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?
    Attached Files Attached Files
    Many thanks for any help, much appreciated.
    Have a great day!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,608 Times in 1,452 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).
    Attached Images Attached Images
    Last edited by RetiredGeek; 2012-02-01 at 06:10.
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 651 Times in 619 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)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts