Edit formula to remove range

[RAJESH1960]

Hello everyone
I have multiple formulas in a worksheet which are with range and I want to edit them and avoid the range. This is one of the formulas. Some of them are array formulas.

=VLOOKUP(D2,CHOOSE({1,2},'2A'!C7:C10000,'2A'!A7:A10000),2,0)

I am trying to edit the formula by deleting the range 10,000 but I keep getting an error like this

=VLOOKUP(D2,CHOOSE({1,2},'2A'!$C7:$C,'2A'!$A7:$A),2,0)

.
If someone can show me how to edit this formula I can try to edit the rest of the formulas accordingly.

 

[RAJESH1960]

=IF(OFFSET($N$1,ROW()-1,MATCH((U$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=U$1*2)*('2A'!$L$7:$L$9947)),0)

=IF(OFFSET($N$1,ROW()-1,MATCH((V$1*2),$N$1:$R$1,0)-1)=0,SUMPRODUCT(--('2A'!$A$7:$A$9947='2A Extract'!$A2)*('2A'!$C$7:$C$9947=$D2)*('2A'!$I$7:$I$9947=V$1*2)*('2A'!$L$7:$L$9947)),0)

 

[johnnyL]

Are those both from the same address?

 

[RAJESH1960]

It is good man. In the original data, It is not showing any error after I replaced the formulas with codes.

 

[johnnyL]

ok

 

[RAJESH1960]

Maybe the sample file is worn out after so many tests....
Thanks JohnnyL.

 

[RAJESH1960]

After converting the formulas into code, time taken
Time elapsed = 8.33561989999725 seconds.

previous time taken around 10 minutes.
One step better.

 

[johnnyL]

Nice!