1

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: =INDEX(Advisors,MATCH(TRUE,ISNUMBER(SEARCH(Advisors,L3613)),0))

Advisors = Defined list of people (also growing)

1
  • The formula you have shown is producing error,,, in fact the Search part is not proper ! And to speed up the process in comparison to the Formula MACRO is the best option ! Oct 6, 2018 at 7:21

1 Answer 1

0

Your question is not totally clear to me. It is difficult to understand what you are exactly doing without giving indications on what is stored in advisors and l3613. What do you have in l3613 ? Is it a single mail ?

Based on what I understood from your question, I would do the following:

Put the following formula next to the name of each advisor:

=match("*"&advisorname&"*";table;0)

This will give you the index in the table of the 1st occurence of each advisor. You may then select from that row the minimal number from the subset of advisors you are looking for (e.g. with a minifs function).

The advantage, here, is that you use the wildcard matching from the match function (avoiding nesting 2 search functions). Also, the match will stop at the first occurence of each advisor and will not look till the end of the list when it is not needed. Ultimately, you may assume that there will be a match for all of them and that the new emails will never be parsed, because it is not necessary.

Example of usage:

enter image description here

Please forgive me if I have not fully understood your issue. I hope this will help you anyway in solving your problem.

2
  • Hi Vincent, not sure if I understand your formula. The "Advisors" array points to a list of names in another sheet that is the criteria for my search. The L3631 is the content it's looking at for that row (row 3631). Do you recommended that I insert your match formula in my criteria array (i.e. Advisors range)? Thanks Sep 28, 2018 at 10:17
  • Hi, I have added a link to a picture with an example. This should be worth a 1000 words ! If not sufficient, feel free to ask for more details. Sep 28, 2018 at 14:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .