# 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.

LVL 6
###### 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.

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.
Commented:
Why don't you try a pivot table ?
MedicineAuthor 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.
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.
Slow-Formula.xlsm

Experts Exchange Solution brought to you by