Formula returning rogue results

[ATSJ]

Here's my table...

A	B	C	D
1	12	A	Mark
2	16	B	Mark
3	37	C	Alice
4	2	D	Alice
5	5	E	Alice
6	23	F	Alice
7	12	G	Alice

<tbody>
</tbody>

I have an array formula that I'm using to return just the values in column A (in ascending order) that match the name 'Mark'. The array formula is

=IFERROR(INDEX(A$2:A$8,SMALL(IF(C$2:C8="Mark",ROW(A$2:A$8)-ROW(A$2)+1),ROWS(A$2:A2))),"")

After posting it in the first row, doing Shift-Ctrl-Enter and copying it down I hit a problem.

The first two entries are found successfully returning ID 1 and 2. Thereafter, the additional rows that I copied the formula into show 0 instead of a blank.

Surely the double bracket at the end of the formula tell it to return a blank cell if nothing is found?

Any help and a solution is appreciated.

 

[Aladin Akyurek]

The sample from A:D you post does not correlate well the non-working formula you show. Is it possible that you provide the output that must obtain for the posted sample?

 

[ATSJ]

Sorry, I pasted in the wrong formula completely. D'oh

It should be

=IFERROR(INDEX(A$2:A$8,SMALL(IF(D$2:D8="Mark",ROW(A$2:A$8)-ROW(A$2)+1),ROWS(A$2:A2))),"")

So what I'm looking for it to return is a list of column A values that correspond to the name but with scope below it should I add more values to the original table

In this case, returning values that match up with Mark should produce...

1
2

Doing likewise for Alice should return

3
4
5
6
7

Instead, however, the returned list is

1
2
0
0
0
0

for as many rows as I copy the array formula into.

 

[Aladin Akyurek]

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(A$2:A$8,SMALL(IF(D$2:D$8="Mark",ROW(A$2:A$8)-ROW(A$2)+1),ROWS(F$2:F2))),"")

 

[ATSJ]

Thanks, that works perfectly when I restrict the formula to just the 7 rows of data currently in the data table, but say I know I'm going to add more rows later and wanted to include those in the formula now so I don't have to edit and copy down the formula every time I add data. Where there is currently no data, a 0 appears when the formula is run.

In other words, it returns...

1
2
0
0
0
0
etc.

Is there anyway that 0 can be edited out to a blank field instead.

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(A$2:A$8,SMALL(IF(D$2:D$8="Mark",ROW(A$2:A$8)-ROW(A$2)+1),ROWS(F$2:F2))),"")

 

[Aladin Akyurek]

Thanks, that works perfectly when I restrict the formula to just the 7 rows of data currently in the data table, but say I know I'm going to add more rows later and wanted to include those in the formula now so I don't have to edit and copy down the formula every time I add data. Where there is currently no data, a 0 appears when the formula is run.

In other words, it returns...

1
2
0
0
0
0
etc.

Is there anyway that 0 can be edited out to a blank field instead.

That should not happen... Assuming that you want return values from column A whenever column D equals to some name:

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Field-1	Field-2	Field-3	Field-4		Mark	Alice
2​	1	12	A	Mark		1​	3​
3​	2	16	B	Mark		2​	4​
4​	3	37	C	Alice			5​
5​	4	2	D	Alice			6​
6​	5	5	E	Alice			7​
7​	6	23	F	Alice
8​	7	12	G	Alice
9​
10​

In F2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$12,SMALL(IF($D$2:$D$12=F$1,ROW($A$2:$A$12)-ROW(A$2)+1),ROWS(F$2:F2))),"")

The formula is adapted for returning multiple sublists. Whenever the ranges are changed, the formula must be changed to reflect those changes. Each edit requires confirming the formula again with control+shift+enter. Note that the formula can be modified to work with dynamic named ranges so that the formula doesn't need changing whenever the ranges grow or shrink.

 

[Drrellik]

If you do still have 0's where no data is present, I think you can go to FILE, OPTIONS, ADVANCED and uncheck show ZERO's for your workbook.