Faster than a look up formula?

[Blunder1]

Hi,

I have two sheets in a workbook containing over 20,000 rows in each. On both sheets in Column A they share a common reference on a majortiy on the rows. Sheets 1 Column A contains the ref, Column M and N contain numbers. Sheet 2 column A contains the ref and column L and M contain the numbers. In my code i have a formula as per the below so i can sum the difference of the two number columns for each matching ref.

Sheet2
=L2-vlookup(a2,Sheets1!A:N,13,0)
=M2-vlookup(a2,Sheets1!A:N,14,0)

This take ages to run though so was hoping someone may have a faster but if code i can use

Thanks in advance

Blunder

 

[JackDanIce]

Instead of VLOOKUP, try:

=L2-INDEX(Sheets1!A:N,MATCH(A2,Sheets1!A:A,0),13)
=M2-INDEX(Sheets1!A:N,MATCH(A2,Sheets1!A:A,0),14)

 

[MrKowz]

Lookup-style formulas are inheritantly slow, but given that your data being returned is numerical, we might have a way around this.

In column A of your Sheet1, are each of the values there unique (not repeated)?

 

[Blunder1]

yes, all references are unique..

Thanks

 

[MrKowz]

Try these formulas out:

=L2-SUMIF(Sheets1!A:A,A2,Sheets1!M:M)
=M2-SUMIF(Sheets1!A:A,A2,Sheets1!N:N)

 

[shg]

Also posted at http://www.excelforum.com/excel-programming/769625-faster-than-a-look-up.html