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:Advisors = Defined list of people (also growing)
=INDEX(Advisors,MATCH(TRUE,ISNUMBER(SEARCH(Advisors,L3613)),0))
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
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...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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)....
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
lesaiot29 
Register To Reply
Bookmarks