[Solved] #N/A from VLOOKUP in first row

[endbrown]

I have a spreadsheet with several tabs that pull values from a tab labeled "rates". The rates are 11 rows (1-11) and three columns. Column A is a beginning value, column B is the ending value, and Column C is a percentage.

Here is the formula I am using, using cell B12 in tab 1 as an example:

=VLOOKUP(B12;Rates;3;1)

For some reason, it only works if I put a value in Cell B12 that fits within the ranges in rows 3 - 11. If I put a value into Cell B12 that is within the ranges of either rows 1 or 2, I get "#N/A".

What am I doing wrong?

Thank you

[FJCC]

Are the values in column A of Rates sorted in ascending order? The 1 you have as the fourth parameter tells VLOOKUP that the values are sorted.

It would be very helpful if you uploaded an example document. We would only need the 11 rows on the rates sheet and an example of a failing VLOOKUP function. To upload a document, click Post Reply and look for the Attachments tab just below the box where you type a response.

[karolus]

The rates are 11 rows (1-11) and three columns. Column A is a beginning value, column B is the ending value, and Column C is a percentage.

Here is the formula I am using, using cell B12 in tab 1 as an example:

=VLOOKUP(B12;Rates;3;1)

For some reason, it only works if I put a value in Cell B12 that fits within the ranges in rows 3 - 11. If I put a value into Cell B12 that is within the ranges of either rows 1 or 2, I get "#N/A".

Looks like your »Rates« is a named-reference to …A3:C11 but not A1:C11 as you assume?

[endbrown]

Here is what I have in the Rates Tab:
-------A--------B-------C
1)--1000----1300----29.5%
2)--1301----1600----30.0%
3)--1601----1900----30.5%
4)--1901----2200----31.0%
5)--2201----2500----31.5%
6)--2501----2800----32.0%
7)--2801----3100----32.5%
8)--3101----3400----33.0%
9)--3401----3700----33.5%
10)--3701----4000----34.0%
11)--4001----4300----34.5%

The formula I have on the other tabs is "=VLOOKUP(C45;Rates;3;1)" where C45 is the cell that I want to calculate the percentage of.

For some reason, if the value is (Let's say) 1050, I get "#N/A". If the value is over 1300 then the proper percentage calculates.

I'm not sure what I'm doing wrong.

I appreciate your help.

[karolus]

Here is what I have in the Rates Tab:
-------A--------B-------C
1)--1000----1300----29.5%
2)--1301----1600----30.0%
3)--1601----1900----30.5%
4)--1901----2200----31.0%
5)--2201----2500----31.5%
6)--2501----2800----32.0%
7)--2801----3100----32.5%
8)--3101----3400----33.0%
9)--3401----3700----33.5%
10)--3701----4000----34.0%
11)--4001----4300----34.5%

The formula I have on the other tabs is "=VLOOKUP(C45;Rates;3;1)" where C45 is the cell that I want to calculate the percentage of.

the Sheetname without any CellAddress is not valid !
use:

Code: Select all

=VLOOKUP(C45;Rates.$A$1:$C$11;3;1)

OR check and fix the RangeAddress BEHIND the Named-Range »Rates« … it should be $Rates.$A$1:$C$11 (⇒Sheet⇒NamedRanges…⇒Manage…)

btw. there is no need for Column B in your LOOKUP-table.

[endbrown]

That did it.
Thank you very much.
Hope you have a phenomenal day.