Lookup formula return multiple results

I have found a good explanation of formula by patrick, but i have a problem, the data is too much more than 70000 rows .

now this example formula =IFERROR(INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1))),"" )  for small number of rows. works. but when it comes to 70K plus rows then it really becomes useless. i learned from Microsoft page that using Row functions that are extremely volatile make the workbook calculation and speed TOO SLOW.

i was wondering if there is any other formula or any other UDF or VBA that can help me reduce the number minutes i am waiting for the calculation to be done. sometimes the excel is frozen and it is really annoying.

also if someone could help with alternative not using ROWs functions in that referred formula.

thanks in advance for your help.
LVL 6
Flora EdwardsMedicineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
An array formula for 70000+ rows is definitely an overkill.
Few suggestions:

1) Try replacing the ROW(1:1) part with ROWS(A$1:A1) and see if this makes any difference though I doubt.

2) You may also consider setting the Calculation Options to Manual so that formulas on the sheets are not recalculated all the time

3) Insert some helper columns if required and break your formula into smaller regular formulas.

4) The last resource is to do it via VBA.
krishnakrkcCommented:
Why don't you try a pivot table ?
Flora EdwardsMedicineAuthor Commented:
I've requested that this question be deleted for the following reason:

thank you guys, but i could not make this work. so deleting the question.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As I said you will need to switch to the VBA alternative to this done.
In the attached, you will notice that there are 76001 rows of data in col. A & B and lookup value is in C2 and the code returns the result in col. D. You may click the button to populate the results.
If you do this through the same Index/small array formula, you will have the same issue what you were talking about.
Hope you will find this helpful and adopt this to your project accordingly.
Slow-Formula.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Flora EdwardsMedicineAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.