Here's my table...

<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

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.

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: