Lookup formula return multiple results

Flora Edwards
Flora Edwards used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Why don't you try a pivot table ?
Flora EdwardsMedicine

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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
Flora EdwardsMedicine

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial