Array Formula not displaying results

[pluginguin]

Please check out my Excel Range.
The Array Formula should copy all ItemCodes from Sheet 1 to Sheet 2, where "Below Stocklevel" value = 1.
But somehow it's doesn't do anything. What am I missing?

This is the source table: (Sheet1)

ItemCode	Below StockLevel
1300011​	0​
130021​	0​
130022​	0​
130023​	0​
130024​	0​
130025​	0​
300031​	0​
300101​	0​
300102​	1​
300103​	0​
300104​	0​
300141​	1​
300142​	0​
300143​	1​

And this is the result table (sheet2) with the array formula

ItemCode

{=IF.ERROR(INDEX(Sheet1!A$2:A$15;SMALL(IF(B$2:B$15=1;ROW(A$2:A$15));ROW(1:1))-1;1);"")}

I expect to see the values 300102 and 300143 but in stead I see nothing.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[pluginguin]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Just updated my account details (Windows and Office 2019)

 

[Fluff]

Thanks for that, how about

=IFERROR(INDEX(Sheet1!$A$2:$A$15,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$15)-ROW(Sheet1!$A$2)+1)/(Sheet1!$B$2:$B$15=1),ROWS(A$2:A2))),"")

 

[pluginguin]

Hi Fluff,

Yep, that function is working.
But I have another issue now.
The example I gave you was flat data.

My actual sheet1 contains data imported from a csv that refreshes periodically.
Each column of sheet1 is a defined range, so that it always looks at the correct amount of rows, also when the amount of rows changes after an update.

To work in my worksheet I need to modify the ranges in your formula and there are 2 parts of the formula I don't know how to modify so that it still works.

• In sheet1, column A has a defined range called "Stock"
• In sheet1, column B has a defined range called "StockLevel"

This is how I have modified your formula

=IFERROR(INDEX(Stock,AGGREGATE(15,6,(ROW(Stock)-ROW(Stock!$A$2)+1)/(StockLevel=1),ROWS(A$2:A2))),"")

The parts I'm unsure about are:
-ROW(Stock!$A$2)+1)
and
ROWS(A$2:A2)

The modified code now displays a result, but when I copy down the formula, each row gets the same data. And the single result I do get is incorrect.
Can you understand what's happening with these defined ranges and how to incorporate them into the formula correctly?

 

[Fluff]

To use named ranges you need to do it like

=IFERROR(INDEX(Stock,AGGREGATE(15,6,(ROW(Stock)-MIN(ROW(Stock))+1)/(StockLevel=1),ROWS(A$2:A2))),"")

Also both named ranges need to be the same size.

 

[pluginguin]

Yes, the named ranges are always the same size.

I am a bit closer now.
The first result now is a correct result.
But when i copy down the formula I still get the same result on each row.

 

[Fluff]

It should work

++Fluff.xlsm
	A	B
1	ItemCode	Below StockLevel
2	1300011	0
3	130021	0
4	130022	0
5	130023	0
6	130024	0
7	130025	0
8	300031	0
9	300101	0
10	300102	1
11	300103	0
12	300104	0
13	300141	1
14	300142	0
15	300143	1
Sheet1

++Fluff.xlsm
	A
1
2	300102
3	300141
4	300143
5
6
7
Sheet2

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(INDEX(Stock,AGGREGATE(15,6,(ROW(Stock)-MIN(ROW(Stock))+1)/(StockLevel=1),ROWS(A$2:A2))),"")

Named Ranges
Name	Refers To	Cells
Stock	=Sheet1!$A$2:$A$15	A2:A7
StockLevel	=Sheet1!$B$2:$B$15	A2:A7

Are you sure that the stock levels are actual numbers & not text.

 

[pluginguin]

They are a formula result. But the format of that column is "Number".
On the example file the formula is working as you said it should.
Sadly not on the file it should be working on.
Can I share my original file somewhere, so you can have a look there?

 

[pluginguin]

Hmm.... I think something must have been stuck in the array formula before. I decided to delete the entire array formula from the column and recreate it. Now it's working fine!
Thanks a lot!