Flora Edwards
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.
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
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.
Why don't you try a pivot table ?
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.
thank you guys, but i could not make this work. so deleting the question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
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.