I have a user with a three sheet workbook. There are two named ranges (on
sheets 2 & 3) which are used in vlookup functions on sheet 1. The VLOOKUP
works fine in the first 12 rows of sheet 1. On row 13, it keeps returning
the error A VALUE IS NOT AVAILABLE TO THE FORMULA OR FUNCTION. Here are the
formulas:

=VLOOKUP(D26,dfcc,2,FALSE)
=VLOOKUP(D27,uscc,2,FALSE)

I can copy the cell with the error result and paste it in a cell in rows 1 -
12. Works fine. I can enter a successful lookup value from the first 12
rows in column d anywhere below row 13 and it fails.

I have never seen anything like this and don't have a clue how to fix it.
Can anyone give me an idea? Thanks very much for your insight.

--
Ann Scharpf

Just a guess - do the range names use absolute cell references? If not,
that's likely your problem - change them to absolute references

"Ann Scharpf" wrote:

I have a user with a three sheet workbook. There are two named ranges (on
sheets 2 & 3) which are used in vlookup functions on sheet 1. The VLOOKUP
works fine in the first 12 rows of sheet 1. On row 13, it keeps returning
the error A VALUE IS NOT AVAILABLE TO THE FORMULA OR FUNCTION. Here are the
formulas:

=VLOOKUP(D26,dfcc,2,FALSE)
=VLOOKUP(D27,uscc,2,FALSE)

I can copy the cell with the error result and paste it in a cell in rows 1 -
12. Works fine. I can enter a successful lookup value from the first 12
rows in column d anywhere below row 13 and it fails.

I have never seen anything like this and don't have a clue how to fix it.
Can anyone give me an idea? Thanks very much for your insight.

--
Ann Scharpf

I'm having the same problem, but mine stops at row 374 of a 3344 row sheet. and i'm using absolute ref.

=VLOOKUP(F3344,'C:\...Translations.xls]Sheet1'!$B$3:$AL$180,17,FALSE)

=VLOOKUP(F3345,'C:\...Translations.xls]Sheet1'!$B$3:$AL$180,17,FALSE)



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

I don't know what same problem you are refering to, as you don't quote
the earlier posting, but perhaps the value in F3345 is not contained
within the lookup table - are you getting a #N/A error?

Pete

On Aug 23, 2:57 pm, Jennifer Alcantara wrote:
I'm having the same problem, but mine stops at row 374 of a 3344 row sheet. and i'm using absolute ref.

=VLOOKUP(F3344,'C:\...Translations.xls]Sheet1'!$B$3:$AL$180,17,FALSE)

=VLOOKUP(F3345,'C:\...Translations.xls]Sheet1'!$B$3:$AL$180,17,FALSE)

EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com

On 23 Aug, 15:08, Pete_UK wrote:
I don't know what same problem you are refering to, as you don't quote
the earlier posting, but perhaps the value in F3345 is not contained
within the lookup table - are you getting a #N/A error?

Pete

On Aug 23, 2:57 pm, Jennifer Alcantara wrote:



I'm having the same problem, but mine stops at row 374 of a 3344 row sheet. and i'm using absolute ref.

=VLOOKUP(F3344,'C:\...Translations.xls]Sheet1'!$B$3:$AL$180,17,FALSE)

=VLOOKUP(F3345,'C:\...Translations.xls]Sheet1'!$B$3:$AL$180,17,FALSE)

EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com- Hide quoted text -

- Show quoted text -

Possibly the formula you have quoted gives a clue - you said you had a
3344 row sheet, yet your second formula refers to the cell AFTER this
record?