Showing results for 
Search instead for 
Did you mean: 
New Member

Search Across Multiple Columns in Gallery

I've searched everywhere for this answer and have tried various different options for coding the search. None seem to work exactly how I want them to.


I have a gallery that shows parts within our stores. I want the text search box (StoresSearch.Text) to search across the columns "Title", "ItemSubDescription" and "PartNumber". 


I have tried doing it via Search with the below formula;

SortByColumns(Search(Filter('Materials Catalogue', StoresItem = 1),StoresSearch.Text,"Title","ItemSubDescription","PartNumber"),"Title",If(SortDescending1,Descending,Ascending))


And also via Filter with the below;

SortByColumns(Filter('Materials Catalogue', StoresItem = 1, StoresSearch.Text in Title || StoresSearch.Text in ItemSubDescription || StoresSearch.Text in PartNumber),"Title",If(SortDescending1,Descending,Ascending))


The problem I have with both of these options is that the user has to be very accurate with what they're searching (i.e. enter it exactly as it shows and in the same word order) and pretty much know in advance how the item is listed.


As an example, the attached screenshot shows the search term 'hose', which is fine as it brings up items with hose in the Title or ItemSubDescription. However, if I was to type 'hose clip' (like the second screenshot). It now only brings up items that follow this exact search term (including the spaces). I want it to pull through all items that have these two words at any point within the columns listed originally. So looking at the screenshots, this should also pull in the "WL Hose Jubilee Clip".


I cannot figure this one out..!!  

Memorable Member
Memorable Member

Try this


Filter(Sort('Materials Catalogue',Title, Ascending), StoresSearch.Text in Title || StoresSearch.Text in ItemSubDescription || StoresSearch.Text in PartNumber)


Some notes:


1. The 'in' is not deleagable so if you have 0ver 500 records (can be 2000 in setting) it will not find anything outside that range

2. The in operator automatically removes and fields if the are blank so you will only ever see the list with those records.  Using in across columns means it removes blanks base first,  then second then third .   Using StartsWith does not do this and is deleagable

3. If the gallery is a collection then using startswith has the same problem with blanks (it is not known why)


So you couls also try


Filter(Sort('Materials Catalogue',Title, Ascending), StartsWith(Title ,StoresSearch.Text) || StartsWith(ItemSubDescription ,StoresSearch.Text) || StartsWith(PartNumber,StoresSearch.Text))


Thanks for the reply


So just a bit of background on the gallery;

It's driven from a SharePoint list currently and we're confident that the items will never go over 2000, so I have set the delegation limit to 2000 in settings which should cover this requirement. 


I've tried both the codes and have found the following;

With the below, it behaves in the same way as the previous. So using the example I gave, it still only pulls through the 2 results when I enter "Hose Clip".

Filter(Sort('Materials Catalogue',Title, Ascending), StoresSearch.Text in Title || StoresSearch.Text in ItemSubDescription || StoresSearch.Text in PartNumber)


With the below, the problem I find is that if I was to just search "Clip", because nothing starts with Clip, it brings up 0 results.

Filter(Sort('Materials Catalogue',Title, Ascending), StartsWith(Title ,StoresSearch.Text) || StartsWith(ItemSubDescription ,StoresSearch.Text) || StartsWith(PartNumber,StoresSearch.Text))

You need a wild card solution.  I don't believe one exists yet.  In is the closest you can get


I believe you're right. I actually stumbled across the thought of a wildcard solution when using a separate system at work today. I've now spent my evening reading through several threads on workarounds for wildcards but as you say, it doesn't appear to exist.


Real shame as this would make the searching on a gallery really powerful.


Thanks for your suggestions. Appreciated. 

Helpful resources

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,539)