# Formula returning rogue results

#### ATSJ

##### Board Regular
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

Code:
``=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.

Last edited:

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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?

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.

Last edited:
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))),"")

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))),"")

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.

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.

Replies
17
Views
643
Replies
5
Views
5K
Replies
4
Views
4K
Replies
6
Views
690
Replies
8
Views
378

1,196,049
Messages
6,013,107
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back