Hi All
Am I using the right formula? Is there a way that would speed up calculation time.
I have used the formula below to return a values for different matches. There are just over 1000 different combinations. Depending on the match I need to return different 3 different categories, which means the calculation is used in 4 different columns. Also depending on on one of these categories I need a return of one of two column values (I use IF for this one).
The data used can have upward of 10K rows, with Supplier - Creditor - Gross Sales - Nett Sales. With this simple data I need organise it into categories of Supplier Groups - Sectors and Preferences and then certain costs according to the Sector. This then can be sorted into a Pivot to condence the information.
{=INDEX(Supplier!E:E,MATCH(1,(Supplier!$A:$A='Extract Supplier '!$I3)*(Supplier!$C:$C='Extract Supplier '!$J3),0))}
Needless to say I have had to turn of auto-calculate.
Am I using the right formula? Is there a way that would speed up calculation time.
I have used the formula below to return a values for different matches. There are just over 1000 different combinations. Depending on the match I need to return different 3 different categories, which means the calculation is used in 4 different columns. Also depending on on one of these categories I need a return of one of two column values (I use IF for this one).
The data used can have upward of 10K rows, with Supplier - Creditor - Gross Sales - Nett Sales. With this simple data I need organise it into categories of Supplier Groups - Sectors and Preferences and then certain costs according to the Sector. This then can be sorted into a Pivot to condence the information.
{=INDEX(Supplier!E:E,MATCH(1,(Supplier!$A:$A='Extract Supplier '!$I3)*(Supplier!$C:$C='Extract Supplier '!$J3),0))}
Needless to say I have had to turn of auto-calculate.
