This technique provides Power Apps keyword searching capabilities for a large SharePoint list. The idea is to take the column you want to search and break it apart, word by word, into individual ‘index’ columns. Then you can StartsWith() search these columns, which delegates to SharePoint. This column set is what I call a ‘StartsWith() Index’. This method works for most search scenarios because a user is looking for a whole word, and not text within a word.

Note: This is one of several methods to search large SharePoint lists. This particular technique is best suited for simple lists where just a column or two needs to be searched. For more complex scenarios, consider using the ‘Office 365 Groups & SharePoint Search API’ method, or handing the search off to a Power Automate Flow.

To set up this index of columns, create a column for each potential word in the column you want to search, so you can use StartsWith() to find that particular word and thus the record you are searching for.

For this example, we’ll want to index the single line of text column ‘ProjectName’. Looking through all the existing records, we determine 9 columns is sufficient as all projects have 9 words or less. When you create these columns, you must also create a SharePoint index for each column. Keep the column names short and provide a number at the end to help with formula readability. In our example, I created 9 single line of text columns named p1-9 and then immediately indexed each of these columns in SharePoint list settings:

Whatever method you choose to populate the index columns, the pattern is the same: parse out the value in the column you want to index, space-delimited word by word, clean up any symbols if necessary, and write the first word to column p1, second word to column p2, third word to column p3, etc. For example, the phrase: The quick brown fox jumped over the lazy dogs, would have one word in each column, in order, p1-9. This post has some Power Automate methods of creating and maintaining a separate index list.

Once the index is in place, we can Filter() a text box, txtInput.Value, using StartsWith() for each index column, p1-p9:

Sort(
    Filter(ProjectIndexSPList,
        Or(
            StartsWith(p1,txtInput.Value),
            StartsWith(p2,txtInput.Value),
            StartsWith(p3,txtInput.Value),
            StartsWith(p4,txtInput.Value),
            StartsWith(p5,txtInput.Value),
            StartsWith(p6,txtInput.Value),
            StartsWith(p7,txtInput.Value),
            StartsWith(p8,txtInput.Value),
            StartsWith(p9,txtInput.Value)
        )
    ),
ProjectName, SortOrder.Ascending)

The StartsWith() Index pattern is a simple yet effective method to provide your Power Apps with keyword search capabilities that support delegation to any size SharePoint list.