Replace slow formula with macro or UDF

i am having a list of values with multiples.... i want to list them with a macro... at present i am using this formula...it works nice but it takes much time to work. o kindly help me


{=IF(ISERROR(INDEX($A:$C,SMALL(IF($B:$B=$F2,ROW($B:$B)),ROW($1:$1)),1)),"",INDEX($A:$C,SMALL(IF($B:$B=$F2,ROW($B:$B)),ROW($1:$1)),1))}

No, you just need a better formula - entered normally:

=IFERROR(INDEX(A:A,MATCH(F2,B:B,FALSE)),"")

Or you need to explain what you have and what you want, rather than just a formula....

@6SJ: sounds as though there might be multiple matches and the OP wants to list them all, not just the first one. Only a guess, given the limited information.

The first problem that you have is that you are passing entire columns to your array formulas, which means that the formulas need to process 1 million+ cells for each call. And you are not using IFERROR, so the calls are doubled.

In the attached, I changed the formulas to the exact range needed on the first sheet, and used IFERROR(formula,"") instead of IF(ISERROR(formula),"",formula) That is much much faster.

Then on the second sheet, I sorted the data by column B first, then by column A - that changed the formulas needed from array formulas to a single MATCH function to find the first occurence, then simple indexing to get the rest of the data. Many times much faster.