Need to speed up writing INDEx MATCH formula

[hkbhansali]

Hi,

I have an INDEX,MATCH Array formula shown below. The sheet that the Formula is being written to has 50000 rows. This instruction takes 7 to 8 minutes to run.

Please advise a better way. Thank you.

Please Login or Register  to view this content.

[Phuocam]

Create helper column:

=A2&B2&C2&D2

Use:

=VLOOKUP(D3&E3&F3&G3,'Closing Rate'!$E$2:$F$50000,2,0)

[hkbhansali]

Hi Phuocam,

Thank you.. U save my lots of time..it's work Perfect...Thanks a lottttttt...

[FDibbins]

Without a file to test on, this is obviously untested.

Try this non-array formula (no helper)
=IFERROR(INDEX('Closing Rate'!$E$2:$E$50000,MATCH(D3&E3&F3&G3,INDEX('Closing Rate'!$A$2:$A$50000&'Closing Rate'!$B$2:$B$50000&'Closing Rate'!$C$2:$C$50000&'Closing Rate'!$D$2:$D$50000,0),0)),0)

[hkbhansali]

Hi FDibbins

Your Formula works but take same time...Thanks for help...

[FDibbins]

As long as you got your question solved
Thanks for the feedback

[hkbhansali]

Hi,
I have reopen this thread because there is some changes in my formula

please see attached workbook

[Phuocam]

Try:

=IFERROR(INDEX(N:N,MATCH(A2&B2&C2&D2,O:O,0)),"")

[hkbhansali]

Hi Phuocam ,

Your formula works but its take too much time, I have data aprox 5000 rows.

[kvsrinivasamurthy]

Post deleted.

[hkbhansali]

Hi kvsrinivasamurthy

I have copy formula & copied down but f3 getting wrong amount (not exact match)

[kvsrinivasamurthy]

Sorry, Lookup function needs LOOKUP Array in Ascending order.

[kvsrinivasamurthy]

s it possible to sort the LOOKUP table. If not original data Make a copy of that table and sort.

[Bosco]

Or………

Try this non-helper solution :

=SUMIFS('Closing Rate'!E:E,'Closing Rate'!A:A,D3,'Closing Rate'!B:B,E3,'Closing Rate'!C:C,F3,'Closing Rate'!D:D,G3)

Regards
Bosco

[hkbhansali]

Hi Bosco,

I had try your formula before but it's also time consuming