Index Match too slow, is there a VBA solution?

[Annie88]

Hi!

I made an index match array formula that looks like this:

=IF(INDEX(Data!$D$1:$D$809659,MATCH("Status 1"&A2,Data!$B$1:$B$809659&Data!$A$1:$A$809659,0))=0,INDEX(Data!$C$1:$C$809659,MATCH("Status 1"&A2,Data!$B$1:$B$809659&Data!$A$1:$A$809659,0)),INDEX(Data!$D$1:$D$809659,MATCH("Status 1"&A2,Data!$B$1:$B$809659&Data!$A$1:$A$809659,0)))

The problem is that I need to run that formula for ~4000 order numbers (number of formulas) and the data source it runs against is 800K rows. It takes 30+ minutes to run. Is there a VBA solution for this that would be faster, or just a faster formula?

Attached is a sample file. It's matching two columns (order number and order status) and pulling whatever date is on that row for column D. If nothing is in column D, it pulls column C's date.

Any ideas?

Thanks

[shg]

	A	B	C	D
1	Order #	Status	Date Match
2	1	Status 1	10/18/2014	C2: =IF(COUNTIFS(Data!A:A, Sheet1!A2, Data!B:B, Sheet1!B2, Data!D:D, ">0"), SUMIFS(Data!D:D, Data!A:A, Sheet1!A2, Data!B:B, Sheet1!B2), SUMIFS(Data!C:C, Data!A:A, Sheet1!A2, Data!B:B, Sheet1!B2))
3	2	Status 1		Format of C2: m/d/yyyy;;
4	3	Status 1	5/30/2013
5	4	Status 1	1/4/2015
6	5	Status 1	1/29/2014
7	6	Status 1	7/28/2013

[xladept]

Here's a program to put in a standard module of a macro enabled workbook:

Please Login or Register  to view this content.

[CK76]

Do it in two stages?

1. Use advanced filter copy to extract only order numbers that has Status 1 for Order Status.
Set up like below image (Criteria range A1:C3, Copy to $A$5:$F$5). This process should finish in few seconds or less.
0.JPG

2. Then perform look up on resulting data using only the Order Number as criteria.
From your description, lookup range should be about 4000 and will complete near instantaneously.