Forum Discussion
Match / CountIfs or other?
You can use FILTER.
Here's a basic example where records with "red" in a table's 'color' column are retrieved:
=FILTER(Demo,Demo[Color]="red")
Thank you Patrick2788 that works great. When I have used this in my document, it is copying blank cels as 0 into the other sheet. Is there anyway of removing these?
Also, is there a way of using this formula to search across more than one sheet a data row?
Huge thanks,
The easiest way to suppress the 0s being returned is to use custom cell formatting which uses four different slots separated by semicolons:
Positive;Negative;Zero;Text
In this example below, the custom cell formatting code is 0;-0;;General
Notice how the third slot is skipped - there are two consecutive semicolons. Additionally, Excel will shorten the formatting code by dropping "General" but I've included it for illustrative purposes.
Re: filtering for multiple sheets. It's do-able but will require the data be consolidate with VSTACK using a 3D reference.
Another option is to return empty strings instead of blanks
=LET( f, FILTER(Demo, Demo[Color] = "red"), SUBSTITUTE(f, "", "") )
