SORT/FILTER formula

[uk747]

Cat	Red	8​
Dog	Blue	7​
Pig	Red	6​
Elephant	Green	5​
Bear	Yellow	2​

Hi
Trying to Filter A2:C100 Where Column B = "Red" and SORT 3rd Column Ascending. If Red Doesn't appear I want to use the IfEmpty Argument

I've used =SORT(FILTER($A$2:$C$100,($B$2:$B$100 = "Red")),3,1) which works, however I need to edit in case there aren't any results
i.e. =SORT(FILTER($A$2:$C$100,($B$2:$B$100="Red"),"Nothing to See HERE!"))
But when I add the Sort part to this I get a #Value! Error if Red doesn't appear in Column B =SORT(FILTER($A$2:$C$100,($B$2:$B$100="Red"),"Nothing to See HERE!"),2,1)

Result should be

Pig	Red	6​
Cat	Red	8​

 

[Georgiboy]

You could try:

=IFERROR(SORT(FILTER($A$2:$C$100,($B$2:$B$100="Red")),2,1),"Nothing to See HERE!")

 

[uk747]

Thanks but trying to avoid IFERROR argument

 

[Georgiboy]

I could only get it to work by sorting both ranges:

=FILTER(SORT($A$2:$C$100,3,1),SORT($B$2:$B$100,1,1)="Red","Nothing to See HERE!")

 

[Fluff]

You could also do it like

=SORT(FILTER($A$2:$C$100,($B$2:$B$100 = "Red"),{"No data","",""}),3,1)

 

[Georgiboy]

@Fluff Is: {"No data","",""} one part for each column of the array?

I am still learning advanced formula, I mainly use VBA.

 

[Fluff]

That's right, so for 3 columns it's ok, but it would need a different approach if there were a lot of columns.

 

[uk747]

I could only get it to work by sorting both ranges:

=FILTER(SORT($A$2:$C$100,3,1),SORT($B$2:$B$100,1,1)="Red","Nothing to See HERE!")

That workded for no results but didnt get both red values

pig	red	6​
Dog	Blue	7​

 

[uk747]

You could also do it like

=SORT(FILTER($A$2:$C$100,($B$2:$B$100 = "Red"),{"No data","",""}),3,1)

Thanks that seemed to work
Is it possible to edit for partial search i.e. red if it was cred tred etc

 

[Georgiboy]

That workded for no results but didnt get both red values

pig	red	6​
Dog	Blue	7​

Just discount my post, i need to learn some more on these new formulae.