Filter using a formula to return results on different worksheet

On the attached, I want to filter the data! worksheet to only return values that have no information in the "Reason" column C.

I want the results to return on a separate tab/worksheet (as shown).

I assume this is some kind of SUMPRODUCT formula?

Thanks in advance.

Try this in A2:

=IFERROR(INDEX(data!A:A,SMALL(IF(data!$C$2:$C$100="",ROW(data!$C$2:$C$100)),ROWS($1:1))),"") Ctrl Shift Enter

Drag to the right and down.

Originally Posted by 63falcondude

Try this in A2:

=IFERROR(INDEX(data!A:A,SMALL(IF(data!$C$2:$C$100="",ROW(data!$C$2:$C$100)),ROWS($1:1))),"") Ctrl Shift Enter

Drag to the right and down.

Perfect - much obliged.

Happy to help.

Actually, I'd like to add a little more to this.

I want to return the rows where there is a value in the Charge column B BUT no value in the Reason column C

I'm manipulated the example again to show the results I'm trying for.

Thanks again.

And sorry - I can't use a pivot table for this.

Change made in purple:

=IFERROR(INDEX(data!A:A,SMALL(IF((data!$B$2:$B$100<>"")*(data!$C$2:$C$100=""),ROW(data!$C$2:$C$100)),ROWS($1:1))),"") Ctrl Shift Enter

Many thanks - one last thing, can I ignore 0 values?

New example attached that should illustrate where I don't want to see 0 values.