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

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

 

[Pete]

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.

 

[Pete]

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.

 

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

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.

 

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

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.

 

[Max]

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

 

[Max]

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

 

[Pete]

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.

 

[Pete]

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.

 

[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 here:
http://contextures.com/xlNames01.html#Dynamic

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.

 

[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 here:
http://contextures.com/xlNames01.html#Dynamic

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.

 

[bnbspop]

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?

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.

 

[bnbspop]

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?

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]

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

That's what that 4th parm is for.

 

[Dave Peterson]

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

That's what that 4th parm is for.