Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chris Kellock
 
Posts: n/a
Default VLOOKUP not returning results

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!
  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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

-----Original Message-----
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!
.

  #4   Report Post  
Chris Kellock
 
Posts: n/a
Default

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.

"Don Guillett" wrote:

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.
--
Don Guillett
SalesAid Software

"Chris Kellock" <Chris
wrote in message
...
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!




  #5   Report Post  
Chris Kellock
 
Posts: n/a
Default

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.

"Jason Morin" wrote:

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

-----Original Message-----
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!
.




  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!



  #7   Report Post  
Chris Kellock
 
Posts: n/a
Default

=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!




  #8   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!



  #9   Report Post  
Chris Kellock
 
Posts: n/a
Default

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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!




  #10   Report Post  
Frank Kabel
 
Posts: n/a
Default

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!





  #11   Report Post  
Chris Kellock
 
Posts: n/a
Default

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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!




  #12   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
see:
http://www.dicks-blog.com/archives/2...ries/trackback

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!



  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,069
Default VLOOKUP not returning results

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.

--
qwerty


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!




  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default VLOOKUP not returning results

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

On Mar 18, 1:17*pm, John wrote:
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.

--
qwerty



"Frank Kabel" wrote:
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


--
Regards
Frank Kabel
Frankfurt, Germany


Chris Kellock wrote:
=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" wrote:


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


--
Regards
Frank Kabel
Frankfurt, Germany


Chris Kellock wrote:
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!- Hide quoted text -


- Show quoted text -


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default VLOOKUP not returning results

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

"John" wrote:

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.

--
qwerty


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
=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" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

Chris Kellock wrote:
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!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. Martin Excel Discussion (Misc queries) 2 December 13th 04 12:21 PM
troubleshoot vlookup returning #N/A dillon Excel Worksheet Functions 1 December 2nd 04 04:32 PM
Need help with modifying VLookUp Tom Excel Discussion (Misc queries) 4 December 2nd 04 01:44 AM
Pulling hair out with VLOOKUP Confused Excel Worksheet Functions 5 November 22nd 04 06:05 PM
VLOOKUP not working Scott Excel Worksheet Functions 3 November 12th 04 09:06 PM


All times are GMT +1. The time now is 06:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"