Search to return all variances

[nickhills1]

Hi Excel-ites

Hoping you can please help.

As you can see in my example, i am trying to create a search capability which will return all fields that contain that term..

e.g. if User types "Aard" into the Orange box search term it will only return I3 and J3 into the yellow Results field (D3 and E3)
But if the User types Cow into the search term, it will return I6, I7, I8 and J6, J7, J8 to the yellow Results field.

I tried to use XLookup, but that only brings back a single result...

Any tips/advice greatly appreciated

Thanks
Nick

 

[Fluff]

How about

=FILTER(I3:J100,ISNUMBER(SEARCH(B3,I3:I100)))

 

[nickhills1]

=FILTER(I3:J100,ISNUMBER(SEARCH(B3,I3:I100)))

That seems to work well Fluff..

Many thanks

 

[Fluff]

You're welcome & thanks for the feedback.