Speed up Excel Index Match - First Instance

[lesaiot29]

I'm using the below formula to look up the first instance of a name appearing in an email (Column L). I have around 40,000 rows and the formula is getting slower and slower as new rows (emails) come in.

Would you know of a way to speed up the formula at all?

Formula:

Please Login or Register  to view this content.

Advisors = Defined list of people (also growing)

[Pete_UK]

Not sure why you've got the ISNUMBER/SEARCH combination in your formula, but I'm sure there must be some reason rather than the more obvious approach:

=INDEX(Advisors,MATCH(L3613,Advisors,0))

maybe with IFERROR( ... ,"") around it.

You could also try this:

=VLOOKUP(L3613,Advisors,1,0)

Hope this helps.

Pete

[Glenn Kennedy]

I'm not clear what you are trying to do, but an array formula will be slow...

=IF(ISNUMBER(MATCH(L3613,Advisors,0)),L3613,"")

see sheet, with formula in A3...

[lesaiot29]

I've tried VLOOKUP but because I'm searching through the email content for the advisor's signature, VLOOKUP doesn't seem to work.

I've also tried your formula Glenn and it returns "".

I'm using the search function to look through the content of the email and find the first instance of the name (in the signature)....

[Glenn Kennedy]

Post a small sample file. A few rows, with suitably anonymised names. Mickey Mouse is a stalwart...

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.