Need help with Index/Match Formula that omits values already populated

[LeftyLayns]

I run a report that has a list of numbers, and only need the numbers below the 0's returned in a column or sheet. Here's what the final product should be:

[TABLE="width: 234"]
<tbody>[TR]
[TD]Report:[/TD]
[TD][/TD]
[TD]Forumla Result:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]8005[/TD]
[/TR]
[TR]
[TD]8005[/TD]
[TD][/TD]
[TD]5364[/TD]
[/TR]
[TR]
[TD]7163[/TD]
[TD][/TD]
[TD]3667[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD]1971[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5364[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4912[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4398[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3667[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2139[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1971[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I have an array formula that omits results already found that looks like this:
=(IFERROR(INDEX($A$2:$A$19,MATCH(0,IF(ISBLANK($A$2:$A$19),1,COUNTIF($C$1,$A$2:$A$19)),0)),""))

and as the fomula is pasted down what is Column C where $C$1 is changed to $C$1:C2 in row 3 to keep continuity and that gets me this formula result:

[TABLE="width: 64"]
<tbody>[TR]
[TD]Forumla Result:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]8005[/TD]
[/TR]
[TR]
[TD]7163[/TD]
[/TR]
[TR]
[TD]5364[/TD]
[/TR]
[TR]
[TD]4912[/TD]
[/TR]
[TR]
[TD]4398[/TD]
[/TR]
[TR]
[TD]3667[/TD]
[/TR]
[TR]
[TD]2139[/TD]
[/TR]
[TR]
[TD]1971[/TD]
[/TR]
</tbody>[/TABLE]



I just cannot for the life of me figure out how to alter the formula to only target for numbers below the 0's.

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not sure why you have one 0 in your expected result? If it needs to be there please explain why. Otherwise, try this, copied down. Note the different row range references in the formula.

Excel Workbook
	A	B	C
1	Report:		Forumla Result:
2	0		8005
3	0		5364
4	8005		3667
5	7163		1971
6	0
7	0
8	5364
9	4912
10	4398
11	0
12	3667
13	2139
14	0
15	0
16	0
17	0
18	1971
Below 0

 

[LeftyLayns]

Peter, this works magnificently!

Id like to learn a little if you have time to explain this wizardry.

What exactly is the aggregate 'small' function doing:

Why are the row range references different; A3:A18 and A2:A17?

Whats the purpose of subtracting ROW(A3)?

And what I really cannot wrap my head around is the division and multiplication.

Would you be so kind as to break this down?

Thanks again!

 

[Peter_SSs]

Peter, this works magnificently!

Id like to learn a little if you have time to explain this wizardry.

Glad it worked for you.

Answers below as best I can.

What exactly is the aggregate 'small' function doing:
It is finding the smallest row number (see more below) that meets the required conditions (value <> 0 and value above = 0) (see more below)

Why are the row range references different; A3:A18 and A2:A17?
Because we are looking at one set of rows to check that they are not zero and at another set of rows (the ones immediately above) to see that they are zero. The first row that could possible meet all the conditions is row 3 so that range starts there, while the rows that could be zero starts at row 2.

Whats the purpose of subtracting ROW(A3)?
We are not only subtracting ROW(A3) but also adding 1. Take a smaller example
(ROW(A$3:A$5)-ROW(A$3)+1)
{3,4,5}-3+1
{0,1,2}+1
{1,2,3}
That is, we have produced an array that simply numbers the rows (from 1) in the range we are looking at. The number sequence could be generated a bit more simply but this way doesn't fall over if new rows are subsequently added at the top of the sheet.

And what I really cannot wrap my head around is the division and multiplication.
So we have the array of row numbers from above {1,2,3,4,5,6, ...}
We now multiply our conditions together. That is (A3:A18 is not zero) * (A2:A17 is zero)
This multiplication will return 1 (True * True = 1 * 1 = 1) for rows that meet both conditions and 0 for rows that don't. So the first few results of this multiplication with the sample data will be
{0,1,0,0,0,1,...}
When we now do the division we get
{1,2,3,4,5,6, ...}/{0,1,0,0,0,1,...}
{Error,2,Error,Error,Error,6,...}
We set Aggregate to ignore errors (2nd argument was 6) so the
- first aggregate formula pulls out the smallest (ROWS(C$2:C2) = 1) number (2) so we get the 2nd number from the indexed range (A3:A18) which is A4 (8005)
- second aggregate formula pulls out the 2nd (ROWS(C$2:C3) = 2) smallest number (6) which results in A8 (5364) being the 6th number from the indexed range
- etc