Index/match - one criteria > drag formula

[MadsHusted]

Hi,

I've run into an issue with this array formula: =IFERROR(INDEX(Sheet1!A:A;SMALL(IF(Sheet1!C:C;Sheet1!$A$1;ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A))+1);COLUMNS(Sheet1!$A$1)));"")

I can't copy the arrays but I press ****+ctrl+enter as needed.


What I want it to do is indexing values for sheet 1 column A based on my single criteria in sheet 2 $A$1 AND drag down the formula so that it comes up with all values associated to sheet 2 $A$1.


Link to my test workbook:
https://1drv.ms/x/s!AjXp0Ya_hoO0hmMQXzdoiA0Dw3Ry

Can anybody assist?

 

[AhoyNC]

Try changing your formula to:
When using array formulas it is best not to reference whole columns. If your data will only go down to say row 5000 and starts in row A2 then use something like $A$2:$A$5000. If not the IFERROR function is going to check all 1 million+ rows which can slow things down.

Excel Workbook
	A
1	02 Clothing
2	Article number
3	2000F34821422
4	B43108A1
5	32737CRC
Sheet

 

[MadsHusted]

Thank you very much, this adjustment helped.