Vlookup & Match formula error

[bobkap]

Please see file at this Dropbox link below.

If you enter the number "1" in cell C6, data under "DMG" in column F changes to "#N/A", but columns D and E work the way that they are supposed to with the same formula I cannot figure out why this is happening. If I enter "2" in C6 there are no problems. I just cannot figure out why this is happening.

Any help would be most appreciated.

Mr Excel

Shared with Dropbox

www.dropbox.com

 

[jasonb75]

The error is not in the formula, it is in the way that you have defined the named range 'bk'. To use an approximate match on column A, you need to exclude the headers from the range as the header is breaks the ascending order requirement for approximate matching. If the header was text and everything below was numeric then it would work.

The formula appears to work with other values because of the way that the approximate match algorithm works. When you include the headers it will fail if the match is the first item below the header, the only exception being is if the header happens to have a lower alphanumeric value than the first data record.

 

[bobkap]

THANKS very much!! I may have figured that out on my own in about 8 years.

 

[jasonb75]

I may have figured that out on my own in about 8 years.

When it first happened to me, it didn't take me too long to find a quick fix for it but it was about 10 years before I actually figured out the reason behind it