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

Topic

New Reply

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?

Webmistress!

Reply | Quote

Replies

W.M.

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). :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi

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)

zeddy

Reply | Quote