My Excel 2003 workbook includes the following sort of data:

....
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
....

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

You have omitted the fourth parameter of VLOOKUP which defaults to TRUE...
Lookup values have to be in Acending order when it is TRUE...
Since you are looking for exact matches... add the fourth parameter as below
=VLOOKUP(C8,inventory,4,FALSE)
and copy down

"Pete" wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

What do you mean by C8 is <=N90486 and =N100001

Those are text strings so cannot be <= or =

Where are the formulas entered?


Gord Dibben MS Excel MVP

On Mon, 18 May 2009 14:37:01 -0700, Pete
wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?

I've tried both "true" and "false"; neither helps.
--
Thanks!

Pete.


"Sheeloo" wrote:

You have omitted the fourth parameter of VLOOKUP which defaults to TRUE...
Lookup values have to be in Acending order when it is TRUE...
Since you are looking for exact matches... add the fourth parameter as below
=VLOOKUP(C8,inventory,4,FALSE)
and copy down

"Pete" wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

"less than or equal to" or "more than or equal to".

I have stock numbers from N90001 up to N90486 (so far), and all of them pull
up the data properly; I also have N100001 up to N100008 (again, so far), and
none of them work.
--
Thanks!

Pete.


"Gord Dibben" wrote:

What do you mean by C8 is <=N90486 and =N100001

Those are text strings so cannot be <= or =

Where are the formulas entered?


Gord Dibben MS Excel MVP

On Mon, 18 May 2009 14:37:01 -0700, Pete
wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?

Hit F5 (or ctrl-g or Edit|goto) and type Inventory and hit enter.

Does that selection include all the rows that you expect to be included?

And if you're matching on text, I bet you'll want an exact match. Make sure you
use False as that 4th parm.

And if this doesn't help, maybe there's a difference between the cells that you
think match. Leading/trailing spaces???)

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson

ps.

Make sure that the values contained in the cells are what they're supposed to
be.

Watch out for numbers having a custom number format. Maybe the N only shows up
because of formatting--not because the cell actually contains that N.

What do you see in the formula bar?

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson

Nope. The cells are all formatted "general", and the formula bar shows
exactly what's in the cell.
--
Thanks!

Pete.


"Dave Peterson" wrote:

ps.

Make sure that the values contained in the cells are what they're supposed to
be.

Watch out for numbers having a custom number format. Maybe the N only shows up
because of formatting--not because the cell actually contains that N.

What do you see in the formula bar?

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson

I know what the operators signify.

I just don't understand how a text string can be < or anything


Gord

On Mon, 18 May 2009 15:56:00 -0700, Pete
wrote:

"less than or equal to" or "more than or equal to".

I have stock numbers from N90001 up to N90486 (so far), and all of them pull
up the data properly; I also have N100001 up to N100008 (again, so far), and
none of them work.

Sorry, I didn't explain it well. I'm not actually typing "=" or "<="; I'm
entering a stock number up to and including N90486, or N100001 or above.
Only when (and every time) I enter N100001 or above do I get "#N/A" in all of
my VLOOKUP cells.
--
Thanks!

Pete.


"Gord Dibben" wrote:

I know what the operators signify.

I just don't understand how a text string can be < or anything


Gord

On Mon, 18 May 2009 15:56:00 -0700, Pete
wrote:

"less than or equal to" or "more than or equal to".

I have stock numbers from N90001 up to N90486 (so far), and all of them pull
up the data properly; I also have N100001 up to N100008 (again, so far), and
none of them work.

Did you check the range?

Did you check to see if the value in C8 matched the value in the cell you think
it matched:

=c8=sheet99!x99

Did you check for trailing spaces in the formula bar? You can't see them just
by looking.

Pete wrote:

Nope. The cells are all formatted "general", and the formula bar shows
exactly what's in the cell.
--
Thanks!

Pete.

"Dave Peterson" wrote:

ps.

Make sure that the values contained in the cells are what they're supposed to
be.

Watch out for numbers having a custom number format. Maybe the N only shows up
because of formatting--not because the cell actually contains that N.

What do you see in the formula bar?

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson


--

Dave Peterson

Did you check the range? - I've defined the range to include all of the data,
twice. I don't know how to "check" it once it's defined.

Did you check to see if the value in C8 matched the value in the cell you
think it matched:
=c8=sheet99!x99 - I don't know what this is, but the value is typed exactly
the same in each cell as far as I can tell.

Did you check for trailing spaces in the formula bar? You can't see them
just by looking. - Yes, I checked. There are no trailing spaces.

I am importing the data into the range table, not typing it in. I don't see
why all records below a certain value would be OK, and all records above a
certain value would not.
--
Thanks!

Pete.


"Dave Peterson" wrote:

Did you check the range?

Did you check to see if the value in C8 matched the value in the cell you think
it matched:

=c8=sheet99!x99

Did you check for trailing spaces in the formula bar? You can't see them just
by looking.

Pete wrote:

Nope. The cells are all formatted "general", and the formula bar shows
exactly what's in the cell.
--
Thanks!

Pete.

"Dave Peterson" wrote:

ps.

Make sure that the values contained in the cells are what they're supposed to
be.

Watch out for numbers having a custom number format. Maybe the N only shows up
because of formatting--not because the cell actually contains that N.

What do you see in the formula bar?

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson


--

Dave Peterson

#1. Did you try the F5 stuff described in the earlier post?

#2. You say that there's an exact match for what's in C8 and the first column
of the lookup range.

What's the name of the worksheet that holds that table?
What's the address of the cell that holds that value that you think is a match?

Put this in an empty cell on the sheet that contains that C8 value:

=c8='name of sheet that contains table here'!x99
Change the name of the sheet to match the name of the sheet.
Change x99 to the cell that you think matches C8.

#3. Don't forget to look for trailing spaces in C8.

#4. I still think it's something simple -- like a typing difference or the
range is incorrect.



Pete wrote:

Did you check the range? - I've defined the range to include all of the data,
twice. I don't know how to "check" it once it's defined.

Did you check to see if the value in C8 matched the value in the cell you
think it matched:
=c8=sheet99!x99 - I don't know what this is, but the value is typed exactly
the same in each cell as far as I can tell.

Did you check for trailing spaces in the formula bar? You can't see them
just by looking. - Yes, I checked. There are no trailing spaces.

I am importing the data into the range table, not typing it in. I don't see
why all records below a certain value would be OK, and all records above a
certain value would not.
--
Thanks!

Pete.

"Dave Peterson" wrote:

Did you check the range?

Did you check to see if the value in C8 matched the value in the cell you think
it matched:

=c8=sheet99!x99

Did you check for trailing spaces in the formula bar? You can't see them just
by looking.

Pete wrote:

Nope. The cells are all formatted "general", and the formula bar shows
exactly what's in the cell.
--
Thanks!

Pete.

"Dave Peterson" wrote:

ps.

Make sure that the values contained in the cells are what they're supposed to
be.

Watch out for numbers having a custom number format. Maybe the N only shows up
because of formatting--not because the cell actually contains that N.

What do you see in the formula bar?

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

From the sounds of it, you're looking up/matching TEXT, not numbers. As Dave
had suggested earlier, you should use an EXACT match version for the VLOOKUP,
viz:
=VLOOKUP(C8,inventory,4,0)
or,
=VLOOKUP(C8,inventory,4,FALSE)

Your lookup values: N90486, N100001, etc are not numbers, they are text.
And your current formula: =VLOOKUP(C8,inventory,4)
as-is, would return unreliable results. You may get some seemingly correct
returns as you posted, but its all due to pure chance. As-is, your current
formula's structure is for looking up numbers and the table array's leftmost
lookup column (in your "inventory") MUST also be sorted in ascending order to
ensure correct results. With the suggested EXACT match version, the the table
array's leftmost lookup column need not be sorted.

Success? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---

I missed this post last night!

Finally, this is what was happening:
1. When I imported a new inventory list into my workbook, the cell range
defined by "inventory" was not changing and N10001 was coincidentally the
first row outside that range (found this by using F5). It appears I have to
delete all of the info in the sheet, paste the new info, and redefine
"inventory"; before I was simply pasting over the existing info and trying to
redefine....
2. ...which is why adding "false" didn't help before. I also have to add
this parameter.

I have also bookmarked the Contextures website, so hopefully I won't make
another stupid mistake so public!

Thanks to everyone for their help.
--
Thanks!

Pete.


"Dave Peterson" wrote:

Hit F5 (or ctrl-g or Edit|goto) and type Inventory and hit enter.

Does that selection include all the rows that you expect to be included?

And if you're matching on text, I bet you'll want an exact match. Make sure you
use False as that 4th parm.

And if this doesn't help, maybe there's a difference between the cells that you
think match. Leading/trailing spaces???)

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson

If your data is on a dedicated worksheet, you could change your formula/range
name to use the entire column.

Or

Maybe you can use a dynamic range name that will grow/contract based on the data
on the sheet.

Debra Dalgleish explains dynamic range names he
http://contextures.com/xlNames01.html#Dynamic

Pete wrote:

I missed this post last night!

Finally, this is what was happening:
1. When I imported a new inventory list into my workbook, the cell range
defined by "inventory" was not changing and N10001 was coincidentally the
first row outside that range (found this by using F5). It appears I have to
delete all of the info in the sheet, paste the new info, and redefine
"inventory"; before I was simply pasting over the existing info and trying to
redefine....
2. ...which is why adding "false" didn't help before. I also have to add
this parameter.

I have also bookmarked the Contextures website, so hopefully I won't make
another stupid mistake so public!

Thanks to everyone for their help.
--
Thanks!

Pete.

"Dave Peterson" wrote:

Hit F5 (or ctrl-g or Edit|goto) and type Inventory and hit enter.

Does that selection include all the rows that you expect to be included?

And if you're matching on text, I bet you'll want an exact match. Make sure you
use False as that 4th parm.

And if this doesn't help, maybe there's a difference between the cells that you
think match. Leading/trailing spaces???)

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Pete wrote:

My Excel 2003 workbook includes the following sort of data:

...
N90486 HOND 9 ACO4EXL RED 1HGCS22839A010469
N100001 HOND 10 INSEX RED JHMZE2H73AS001296
...

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)).
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

--

Dave Peterson


--

Dave Peterson

On May 18, 4:37*pm, Pete wrote:
My Excel 2003 workbook includes the following sort of data:

...
N90486 *HOND * *9 * * * ACO4EXL RED * * 1HGCS22839A010469 * * *
N100001 HOND * *10 * * *INSEX * RED * * JHMZE2H73AS001296 * * *
... * *

VLOOKUP is using the first column to pull data (=VLOOKUP(C8,inventory,4)). *
If the value in C8 is <=N90486, the data transfers fine; if it is =N100001,
it returns #N/A and does not offer the "Formula Error Button" next to the
cells with errors.

Suggestions?
--
Thanks!

Pete.

You may need to resort your data, n100001 will "smaller" than N90486.
Vlookup is dependent on the table array being sorted from least to
most.

Cheers.

If you're looking for an exact match, this isn't true.

That's what that 4th parm is for.

bnbspop wrote:

<<snipped

You may need to resort your data, n100001 will "smaller" than N90486.
Vlookup is dependent on the table array being sorted from least to
most.

Cheers.

--

Dave Peterson