Should excel take this long to process a match index formula?

Lars,

It would surprise me if VBA would run faster in such cases. Damon, Ivan... any comments on this?

Just curious: Would you post the formula that you use?

Aladin

Sounds like your data might have very few successful
matches and that you're match type (3rd argument)
is 0 or FALSE. Is that the case? Are you interested
in tuning your formula/approach? Can you sort your data
based on the lookup array or column?

Re: Should excel take this long?

Aladin and Lars,

Aladin is right, VBA should be slower. A lot slower. However, I can not imagine how Excel can be so slow as to take 3 hours with such a small amount of data. "Something is fishy in Denmark."

Occasionally VBA has an advantage over Excel built-in formulas for tasks where an algorithm that is customized and optimized for the particular task at hand can be much more efficient. Usually the only advantage of VBA is the additional or special functionality it can provide.

Yes, I think we should take a look at that formula.

Damon

Re: Should excel take this long?

Aladin and Lars,

Aladin is right, VBA should be slower. A lot slower. However, I can not imagine how Excel can be so slow as to take 3 hours with such a small amount of data. "Something is fishy in Denmark."

Occasionally VBA has an advantage over Excel built-in formulas for tasks where an algorithm that is customized and optimized for the particular task at hand can be much more efficient. Usually the only advantage of VBA is the additional or special functionality it can provide.

Yes, I think we should take a look at that formula.

Damon

Aladin & Damon

In general I would have to agree with Aladin & Damon, VBA would be slower, BUT that depends on
what exactly he is trying to do. I can't imagine
it would take this long ?? Definitely something
fishy in Denmark. Need to see what he is trying to do?
Any chance Lars ??

Ivan

Re: Should excel take this long?

Alright guys here is the formula:

{=if(iserror(index((carrier1, match($n3&$as3, range1 & range2,0))), "-", index(carrier1, match(($n3&$as3, range1 & range2, 0)))}

and Lars, is right, VBA should be slower. A lot slower. However, I can not imagine how Excel can be so slow as to take 3 hours with such a small amount of data. "Something is fishy in Denmark." Occasionally VBA has an advantage over Excel built-in formulas for tasks where an algorithm that is customized and optimized for the particular task at hand can be much more efficient. Usually the only advantage of VBA is the additional or special functionality it can provide. Yes, I think we should take a look at that formula. Damon : Lars, : It would surprise me if VBA would run faster in such cases. Damon, Ivan... any comments on this? : Just curious: Would you post the formula that you use? : Aladin :

Re: Should excel take this long?

{=if(iserror(index((carrier1, match($n3&$as3, range1 & range2,0))),"-",index(carrier1, match(($n3&$as3, range1 & range2, 0)))}

Lars,

Lets try some simplification here:

(1) =IF(ISNUMBER(MATCH($n3&$as3, range1 & range2, 0)), INDEX(carrier1, MATCH($n3&$as3, range1 & range2, 0)), "-")

It seems you entered the original formula as an array formula: Is that really necessary?

(2) You may even do better by keeping the #N/A values instead of suppressing them. Additional processing that you carry out on the retrieved data can take those #N/A values into account.

Additionally, as Mark pointed out, try to avoid exact matches. If your data consists of text values with no overlap in parts, you might just omit 0 from the above formulas. If I understood Mark well, you might need to sort the data in such a way that the most frequently retrieved values occur first.

Aladin

Also...

I'd eliminate range1&range2 from your formula, and
peform the concatenation in worksheet cells that
the MATCH() function would reference as its 2nd
argument. : Alright guys here is the formula

Thanks to the both of you I will give it a shot