I create worksheet that showing data as below table :-
basic more than 0.01 less than 10.00 = 0
basic more than 10.01 less than 20.00 = 3.00
basic more than 20.01 less than 40.00 = 6.00
I using a code for my table as (1) for more than 0.01 less than 10.00 = 0.... till 20,000.00 and created 300++ code for the table
for my calculation when my basic showing 10.50 should show as 3.00 if more than 20.01 showing 6.00
now I using a formula =LOOKUP(A1,data!A4:A805,date!D4:D805)...A1 was the column to add code, so that not very useful for my payroll work. every time the basic changed I need to refer the table that I create and get the code.
how do I create a formula when I changing my basic then my data can read the value as need?
I downloaded your Excel file.
I took a good look on it.
Observations: (My opinion).
a) The tabs EPF and SOCSO have a Structural Problem with the table. a.1) Merged cells.
a.2) Repeated values on a ordered list.
a.3) Missing conditions for value EQUAL to limit.
These aspects make the automatic search a task almost impossible.
b) These tabs present an unnecessary calculus on cells.
b.1) F4 = D4 E4
b.2) G4 = D4-E4
Is not necessary to have F4 and G4 at your Data Bank.
If on any moment you need these calculus, use then in the formula when necessary.
A lot of unnecessary formulas makes your Excel file bigger and slower, without give you any benefit.
I have a suggestion for you.
I created a tab "NEW SOCSO".
The first 10 codes were reorganized.
At tab "Payroll Report-Jan (1)", I introduced a new formula and two new records to show you as the formula and a new table at "New SOCSO" works.