Performance improvement to find one data

[Fabricio]

Hi,
I need to improve my Excel calculation performance so please help me to better this cell formula.
No VBA, please...

Problem description:
a) For a given value (cell L2) and a given date (cell E2), I need to find which percentual to apply (cell M2).
b) At the bottom of the spreadsheet, I have reference tables to guide which percentual to apply; they are both value and time ranges.
c) If the reference value (cell L2) exceeds to value range, upper percentual value applies.
d) If the reference date (cell E2) exceeds to time range, an error message shall appear.
e) Reference tables can be freely adjusted, while given values/dates can't.

Current formula is at cell M2.

Thanks

 

[bosco_yip]

Hi,

see attached file

Regards

 

[Fabricio]

Hi,

see attached file

Regards

Thanks for the repply; with some minor adjustments, it worked.

But, does that speeds up?
I was wondering if the DGET function would apply, maybe it is a faster function.

My spreadsheet now reaches over 100.000 lines and 50 columns; speed is now my major concern.

Regards

 

[bosco_yip]

...........
I was wondering if the DGET function would apply, maybe it is a faster function............

You're right, DGET function (a IBM Database function) will faster than the Excel Lookup function in your 2 criteria Multi-tables Lookup example,

provided that your input data and output criteria should set-up follow the database specified rule.

Regards

 

[Fabricio]

You're right, DGET function (a IBM Database function) will faster than the Excel Lookup function in your 2 criteria Multi-tables Lookup example, provided that your input data and output criteria should set-up follow the database specified rule.

Could you propose a data arrengement to this specific case?
I've tried it and couldn't find a way to it.

Regards