VLOOKUP not returning results

[Chris Kellock]

I have made several worksheets for lookup up pricing for our various product
lines. These sheets look up a description, list price, and my cost in
another worksheet (in the same XLS file) based on the part number I enter on
the first sheet. The list on the second page has been assigned a name, and
I'm using VLOOKUP to find the part number in that range, and then return the
values into my original sheet. The list is sorted alphabetically by the part
number. I can send the file is someone can help!

My problem is that if the lookup list has over about 5,000 items, the
results of the VLOOKUP are intermittent. I can actually copy and paste a
value from the list into my sheet and it won't look up. Some of my price
lists are over 15,000 items.

Does anyone have any experience with VLOOKUP not working in a large list?
How about any ideas of how to make it work more reliably. I have one sheet
I'm trying to make that I expect to have about 50,000 items in it. I
desperately need to get this worksheet working. Please help!

 

[Don Guillett]

results of the VLOOKUP are intermittent
which means??
I just made a list of 65000 and it worked just fine to find the correct
result. Perhaps your part numbers are a mixture of numbers and text and not
sorted as you expect.

 

[Jason Morin]

I'm speculating that you're not specifying the VLOOKUP
formula to find an exact match. To do this, include a 0
in the fourth argument:

=VLOOKUP(---,---,---,0)

This forces an exact match and does not require your
lookup table to be sorted.

If this doesn't do the trick, email me the file and I'll
take a look. Replace OPPOSITEOFCOLD with the word "hot".

HTH
Jason
Atlanta, GA

 

[Chris Kellock]

Almost every part number I deal with is mixed letters and numbers. I've got
the list formatted as text and removed all dashes and spaces from the
numbers. By intermittent I mean that some part numbers work and some do not.

 

[Chris Kellock]

I've got the lookup set to use exact matches. That's why I get the N/A as a
result. But a part number I know is in the sheet is not being found. Even
when I copy and paste it from the list.

 

[Frank Kabel]

Hi
typical cause would be that either you're searching for a numeric but the
lookup list is stored as 'Text' or vice versa. You may post your exact
formula and the values which do not work

 

[Chris Kellock]

=IF($A4="","",VLOOKUP($A4,'Lookup - Siemens1'!Siemens,2,FALSE))

Lookup-Siemens1 is the name of the workbook (made from a template called
Lookup - Siemens.

Range name Siemens covers 4 coumns in a second sheet in this workbook.

Part number 3SB3400-0A works but 3SB3400-0B (next one in list, both are
around 7800 parts down into the list) won't look up. 54161 will look up
(near top of list), 6ED1052-1FB00-0BA4 will look up, but MSE6 (almost at
bottom of list) will not.

 

[Frank Kabel]

Hi
the only reason for this could be spaces or invisible characters in your
lookup range (or the lookup value cell A4). Try checking the lookup value
and the expected match manually. e.g. with
=$A4='Lookup - Siemens1'!A10
if you would to expect cell A10 on the lookup sheet to match with your
lookup value. If this returns FALSE then you could start checking it
character by character. e.g. try the following formula:
=IF(MID($A$4,ROW(1:1),1)=MID('Lookup -
Siemens1'!$A$10,ROW(1:1),1),"Character: " & ROW(1:1) & "
matches","Character: " & ROW(1:1) & " does NOT match")
and copy this formula down

 

[Chris Kellock]

It appears there were some spaces at the end of some part numbers. I thought
I had stripped them out, but there was still a number of them with spaces.
Fixed that now. I'd love to just dump all my price lists into Access and
then have one Excel sheet that looked up any part number, instead of making
separate Excel sheets for each vendor's part number lookups. But I don't
know how to make Access and Excel talk...

 

[Frank Kabel]

Hi
re: Access <-> Excel. In Excel you can use 'Data - Import External Data' for
this

 

[Chris Kellock]

I've tried that, and it just brings in a bunch of data from the database, not
just a record based on a number I type in. I need a way to type in a part
number in Excel and have it refer out to the database to look up description,
list price, and my cost.

 

[Frank Kabel]

Hi
see:
http://www.dicks-blog.com/archives/2004/12/13/parameters-in-excel-external-data-queries/trackback

 

[John]

I still have issue with vlookup after running all suggested diagnostics

I am running a SS for salaries. I am looking up a ranking percentage for an
employee (column T) from another WS's column B to return the target salary in
the other WS's column D.
=VLOOKUP(T5,TCC!$B$7:$D$1000,3,FALSE)
cell T5 is a formula rounding the percenatage to the thousands place (eg
94.2%)
Column B in the lookup array contains 94.2% as a value but the vlookup
returns N/A. Like Chris, a great percentage of mine work, but maybe 10% do
not.

I have completed the diagnostics suggested here =T6=TCC!B941 this equals
"TRUE"
I have also completed the = code, =value, =istext and =isnumber diagnostics
suggested by ryguy7272 in a similar post. These all indicate exact matches
between the cell contents. I have also done the more obvious formatting
(incl. text to columns ->general), and hard coding the data to no avail.

What is next in my case?

Interestingly, the old Lookup function works fine but i want to avoid using
it.

 

[JP]

John,

You should post your question to a new thread, this one over 3 years
old.

Since you are working with percentages, did you try rounding the value
in T5? Just because they are formatted the same doesn't mean the
comparison is the same.


HTH,
JP

 

[Jerry W. Lewis]

What does =(T6-TCC!B941) return? Note that the parentheses are necessary.

=T6=TCC!B941 is not a reliable indicator of what VLOOKUP sees, due to an
(IMHO) unfortunate "optimization" introduced in Excel 97
http://support.microsoft.com/kb/78113

If, as I expect, =(T6-TCC!B941) returns a non-zero number, then the issue is
slight differenceces due to accumulated roundoff in intermediate
calculations. Too keep VLOOKUP from seeing these slight differences, ROUND
both the lookup and target values to the same number of figures.

Jerry