What do you think would be quicker?

[alexcn]

... and why ...

1) Looking up a multiplier value from another excel worksheet by way of an excel lookup formula?
2) Looking up a multiplier value from an Access database by way of a VBA function in Excel and returning the value?
3) Looking up a multiplier value from an SQL database by way of a VBA function in Excel and returning the value?

Many thanks,

Alex

 

[Stormseed]

Depends on what data, the type of data, size of data, connectivity, etc., you are looking up.

 

[alexcn]

Thanks for reply!

Basically looking up price values / contract specifications in a centralised database (held in either Excel Lists or Access or SQL) So the search would be denoted based on two / three criteria (usually an item code, a department code and an effective date) the data returned would either be a price to 6 decimal places, or a text string no greater than 50 characters.

Connection would be either internal Microsoft.Jet.OLEDB.4.0 on the same network, or an externally hosted SQL server connecting over a standard internet connection.

I am more thinking of semantics than anything else ...

Depends on what data, the type of data, size of data, connectivity, etc., you are looking up.

 

[Fazza]

Hi Alex,

IMO looking up by suitable worksheet function to another worksheet should be fastest. Worksheet functions are intrinsically faster than the other options.

However, I'm assuming a single lookup code. Such as by concatenating - maybe into a hard coded (non-formula) value - the two or three criteria to give a single value that can be matched/looked up to the table.

Sometimes SUMPRODUCT or array formulas are used to do look ups with multiple criteria and these are not efficient and not fast - and in fact can have very poor performance.

If you have multiple fields to match on, an alternative to using a function might be to use SQL - and either join the tables or put the matching criteria in a WHERE clause. If this is suitable for your application, such a database style approach can offer advantages. My guess is this may not suit exactly what you want.

HTH, Fazza

 

[alexcn]

Thanks Fazza - I will investigate your comments and revert in due course.

Hi Alex,

IMO looking up by suitable worksheet function to another worksheet should be fastest. Worksheet functions are intrinsically faster than the other options.

However, I'm assuming a single lookup code. Such as by concatenating - maybe into a hard coded (non-formula) value - the two or three criteria to give a single value that can be matched/looked up to the table.

Sometimes SUMPRODUCT or array formulas are used to do look ups with multiple criteria and these are not efficient and not fast - and in fact can have very poor performance.

If you have multiple fields to match on, an alternative to using a function might be to use SQL - and either join the tables or put the matching criteria in a WHERE clause. If this is suitable for your application, such a database style approach can offer advantages. My guess is this may not suit exactly what you want.

HTH, Fazza