ISNUMBER(MATCH speed up.

[strongman86]

Hi Excel guru's,

Can please someone suggest alternatives to below formula I could use to speed things up. I got "Master" sheet where it's pulling data from with thousands of rows and this formula is working very slow.

=ISNUMBER(MATCH(LEFT(Sheet1!F2,7)&"|"&Sheet1!E2,INDEX(LEFT(Master!$A$2:$A$8000,7)&"|"&Master!$AI$2:$AI$8000,0,0),0))

Kind regards,
Strongman86

 

[Peter_SSs]

What sheet name is this formula on?

I don't know how it will compare for speed, but you could try this

=COUNTIFS(Master!$A$2:$A$8000,LEFT(Sheet1!F2,7)&"*",Master!$AI$2:$AI$8000,Sheet1!E2)>0

 

[strongman86]

What sheet name is this formula on?

I don't know how it will compare for speed, but you could try this

=COUNTIFS(Master!$A$2:$A$8000,LEFT(Sheet1!F2,7)&"*",Master!$AI$2:$AI$8000,Sheet1!E2)>0

Hi Peter_SSs. This is about 10x faster. Thank you very much.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.