VLOOKUP not returning data in some (but not all) records

[Pete]

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?

 

[Sheeloo]

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

 

[Sheeloo]

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

 

[Gord Dibben]

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

 

[Gord Dibben]

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

 

[Pete]

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

 

[Pete]

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

 

[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.

 

[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.

 

[Dave Peterson]

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

 

[Dave Peterson]

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

 

[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?

 

[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]

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

 

[Pete]

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

 

[Gord Dibben]

I know what the operators signify.

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


Gord

 

[Gord Dibben]

I know what the operators signify.

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


Gord

 

[Pete]

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.

 

[Pete]

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.

 

[Dave Peterson]

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.

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