Index Arrays

[Az566]

Hi, I am looking to create an index array. the conditions for the array are based on drop down menus. I want to have it that if the drop down menu has * in it, the array should calculate everything rather than a specific value.

={IF(ROWS(I$14:I14)>$H$7,"",


IFERROR(INDEX('Sheet1'!$C:$C,SMALL(IF(('Sheet1'!$T:$T=Sheet2!$H$2)*('Sheet1'!$U:$U=Sheet2!$I$2)*('Sheet1'!$L:$L>0),ROW('Sheet1'!$T:$T)),


ROWS(I$14:I14))),"")}

In the SMALL array I have three conditions:

1.'Sheet1'!$T:$T=Sheet2!$H$2
2. 'Sheet1'!$U:$U=Sheet2!$I$2
3. 'Sheet1'!$L:$L>0

The array works fine but only if I place an actual value in the drop down cells which are H2, I2. The third condition is just to ensure it only calculates the array for rows that have numbers above 0. My question is how can I amend it so that selecting "*" in the drop down cells will calculate for everything?

 

[pgc01]

Hi
Welcome to the board

This will get you to match conditions 1 and 2 if the respective dropdowns are "*"

IFERROR(INDEX('Sheet1'!$C:$C,SMALL(IF((('Sheet1'!$T:$T=Sheet2!$H$2)+('Sheet1'!$T:$T="*"))*(('Sheet1'!$U:$U=Sheet2!$I$2)+('Sheet1'!$U:$U="*"))*('Sheet1'!$L:$L>0),ROW('Sheet1'!$T:$T))

 

[Az566]

Thanks for the welcome!

Thanks also for the formula but it doesn't seem to work. Now it calculates it when "*" is selected but doesn't return any results. The full formula is:

=IF(ROWS(I$14:I14)>$H$7,"",


IFERROR(INDEX('Lifetime'!$C:$C,SMALL(IF((('Lifetime'!$T:$T=Summary!$H$2)+('Lifetime'!$T:$T="*"))*(('Lifetime'!$U:$U=Summary!$I$2)+('Lifetime'!$U:$U="*"))*('Lifetime'!$L:$L>0),ROW('Lifetime'!$T:$T)),


ROWS(I$14:I14))),""))

 

[pgc01]

Hi

Sorry, you have to test if the value in the dropdown is "*"

=IF(ROWS(I$14:I14)>$H$7,"",


IFERROR(INDEX('Lifetime'!$C:$C,SMALL(IF((('Lifetime'!$T:$T=Summary!$H$2)+(Summary!$H$2="*"))*(('Lifetime'!$U:$U=Summary!$I$2)+(Summary!$I$2="*"))*('Lifetime'!$L:$L>0),ROW('Lifetime'!$T:$T)),


ROWS(I$14:I14))),""))