VLOOKUP CUSTOM FUNCTIONS

[patrick_m84]

Hi guys

I'm kinda new to the concept of Functions, but have recently let my thoughts wonder, and wondered if you could clear something up for me.

A VLOOKUP, obviously is a function that will search for a particular item of data amongst a given range, and return the value at a particular column on that row. Currently, in the company i am in, we use a lot of VLOOKUPs in a Spreadsheet that is linked to a product file of about 40,000 products.

Now my question is this. As far as I can tell, VLOOKUP works on a linear search through data. This is understandable as the data it will be looking at is not necessarily ordered and therefore a binary chop is impossible.... unless the vlookup sorts, then searches, then unsorts.

Does anyone know how the VLOOKUP works? Because if it is a linear search, i may well attempt to write my own cutom function that will search using a chop and significantly higher response time.

[Aladin Akyurek]

I think you have an efficiency problem.

If the lookup table is not sortedin ascending order, you have to use...

VLOOKUP(LookupValue,LookupTable,ColIdx,0)

where the match-type (Help File: range_lookup) is 0 (or FALSE).

This invokes a linear search routine which is expensive.

If the lookup table is sorted in ascending order, you can have...

VLOOKUP(LookupValue,LookupTable,ColIdx,1)

where the match-type is 1 (TRUE).

This invokes a binary search routine which is fast.

If you could restrict lookup to a relevant subrange (subtable) of your lookup table, you could perform better.

[Ponsy Nob.]

It is most unlikely that you will be able to create custom functions that are more efficient than Excels's built-in worksheet functions.

It is, however, often possible to create macros that manipulate the data before applying built-in functions in order to increase efficiency.