Link to home
Start Free TrialLog in
Avatar of Flora Edwards
Flora EdwardsFlag for Sweden

asked on

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.
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

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 ?
Avatar of Flora Edwards

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks