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