Problem with ISBN numbers

[EDUCATED MONKEY]

Windows up professional office up 2003 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
<o> </o>
Can any one tell me what sort of problems arise as a result of searching through list using VLOOKUP, I have used this in the first part of the project and it worked fine now I am faced with searching multiple columns as the range of numbers for one thing is very large and for another the block size varies, I consider that it is necessary to assign a range of these isbn numbers to a column given that the max depth is 65536 is there an optimum column depth so the search speed remains either constant cross columns or optimum across the range of these numbers<o></o>

 

[baitmaster]

I'm not aware of an optimum size for getting data returned from a VLOOKUP, but there are a few things you might consider

If you can possibly get a later version of Excel, you won't be limited to 65,536 rows. XL07 has 1,048,576

VLOOKUP is not always the best way of doing things. People usually hard-code the third input value (column number), meaning it becomes incorrect if columns are inserted

INDEX(MATCH( usually offers more robust, and flexible functionality. You can expand this to INDEX(MATCH(),MATCH() and vary which column you are looking at, based on say whether a search term is found in any column. I would consider this last option based on how I understand your requirement

Alternatively, you could consider using the FIND function within some VBA code, to quickly identify the cell in question (regardless of row or column)and perform any analysis from there, perhaps using OFFSET

There are various ways to approach this, it really depends on what you are actually trying to do

 

[EDUCATED MONKEY]

thanks all good stuff to think about, funny i was think adout up grading as all the excel tutorial seem to be based upon 2007 version

 

[xenou]

I've never used lookups past around 10,000 to 15,000 rows but, that said, performance has always been good *if* the lookup is not in an external, closed workbook.

One tip is (I think) that lookups are always faster in sorted tables when you do not use the exact match option.

 

[shg]

A VLOOKUP with the last argument False runs in O(N) (i.e., linear) time. With the last arument True, it runs in O(log(N)) time. For a lot of rows, the latter could be thousands of times faster.

If you can sort the lookup column, then instead of

=VLOOKUP(A1, A2:E100000, 5, FALSE)

you could use

=IF(LOOKUP(A1, A2:A100000)=A1, LOOKUP(A1, A2:E100000), NA())

 

[EDUCATED MONKEY]

thank you i not sure that i have taken it all but hey! i can read it over again sure it makes good sence