Filter vs Search vs LookUp in Power Apps (Complete Guide)

Three functions, one job that sounds identical: find records. That is exactly why new Power Apps makers pick the wrong one, and then spend an afternoon wondering why the gallery is empty or why the delegation warning refuses to go away.

I have lost count of how many Power Apps apps I have opened where someone used Filter to grab a single record, then wrapped the whole thing in First() to make it behave. It works, but it is the long way round.

In this tutorial, I will show you exactly what each of these three functions returns, when to reach for each one, how they behave with delegation, and the mistakes that cause the most support tickets.

What Filter, Search, and LookUp Actually Do in Power Apps

All three functions belong to the same family in Power Fx. They all reduce a table down to the rows you care about.

The difference is what comes back out.

• Filter returns a table. It can be zero rows, one row, or ten thousand rows.
• Search returns a table too, but you do not write a condition. You hand it a search string and a list of text columns.
• LookUp returns a single record, or a single value if you give it a third argument.

That one distinction, table versus record, causes most of the confusion, because Power Fx will not let you use a table where a record is expected.

Use them when:

• Filter – you need many matching rows, or you need precise logic (>, <, And, Or, date ranges).
• Search – you are building a search box and want a “contains” match across a few text columns.
• LookUp – you need one record, usually to read a property off it, like an email address, a price, or an ID.

Don’t use them when:

• Don’t use Filter to get one record and then wrap it in First(). Use LookUp.
• Don’t use Search for numeric, choice, or boolean comparisons. It only works on text columns.
• Don’t use LookUp when more than one row can match and you actually care about all of them. It silently returns only the first.

The Short Answer

If you only read one section, read this one.

1. Do you need more than one row back? → Filter
2. Is the user typing into a text box and expecting “contains” behavior? → Search
3. Do you need exactly one record or one field value? → LookUp

That is genuinely the whole decision.

Check out Power Apps DateAdd Function

Filter, Search, and LookUp Syntax

Here is the syntax for the Filter function in Power Apps.

Filter( Table, Formula1 [, Formula2, ... ] )

Argument	Required?	What it does
Table	Yes	The data source or collection to search
Formula1	Yes	A condition evaluated for every row. Must return true/false
Formula2…	No	Additional conditions. Multiple formulas are combined with And

Here is the Power Apps Search() function syntax:

Search( Table, SearchString, Column1 [, Column2, ... ] )

Argument	Required?	What it does
Table	Yes	The data source or collection to search
SearchString	Yes	The text to look for. If blank, all rows are returned
Column1…	Yes	One or more text column names, written as quoted strings

Here is the syntax for the Power Apps Lookup function:

LookUp( Table, Formula [, ReductionFormula ] )

Argument	Required?	What it does
Table	Yes	The data source or collection to search
Formula	Yes	The condition. Evaluation stops at the first match
ReductionFormula	No	Reduces the found record to a single value, for example Email

Note: In Search, the column names must be written as text strings in quotes, like "Title". That trips people up because every other function in Power Fx takes them as bare identifiers.

Setting Up the Example Data

Below I have a SharePoint list called Employees with the columns FullName, Department, JobTitle, Email, Salary, and IsActive, and I want to build a screen that lets a user browse, search, and pull details for a single person.

FullName	Department	JobTitle	Email	Salary	IsActive
Marcus Webb	Sales	Account Executive	marcus@contoso.com	74000	Yes
Ana Ruiz	Finance	Controller	ana@contoso.com	96000	Yes
Dan Okafor	IT	Systems Analyst	dan@contoso.com	71000	No
Lena Fischer	Sales	Sales Analyst	lena@contoso.com	62000	Yes

I created the SharePoint list and added those records, and you can see in the screenshot below:

Every example below uses this same list, so you can see the difference between the three functions on identical data.

Check out Power Apps Substring Function: Extract Text Using Left, Mid, and Right

How to Use the Filter Function (Step-by-Step)

I want the gallery to show only active employees in the Finance department.

Here are the steps:

1. Insert a Vertical Gallery onto your screen.
2. Connect it to the Employees SharePoint list when prompted for a data source.
3. Select the gallery, and in the property dropdown to the left of the formula bar, choose Items.
4. Enter this formula:

Filter(
    Employees,
    Department = "Finance",
    IsActive = true
)

How does this formula work?

• Employees is the table being scanned. Every row is evaluated.
• Department = "Finance" is the first condition. It runs once per row, returning true or false.
• IsActive = true is the second condition. Because I passed it as a separate argument, Power Fx joins it to the first with And. Writing Department = "Finance" && IsActive = true gives an identical result.
• Filter always returns a table, even when only one row matches, and even when none match. That is why it drops straight into the Items property without any extra work.

When you preview the app, the gallery shows Ana Ruiz only.

Here is the screenshot for your reference, with the exact output:

Pro Tip: Filter preserves the original column set. If you want a slimmer result, wrap it: ShowColumns(Filter(Employees, Department = "Finance"), "FullName", "Email"). Smaller payloads render noticeably faster on mobile.

Filter with Or logic and a range

Filter(
    Employees,
    Salary >= 65000 && Salary <= 80000,
    Department = "Sales" || Department = "IT"
)

• The first argument handles a numeric range using && inside one expression.
• The second uses || for an either/or on department. The Or logic has to sit inside a single argument, because separate arguments are always joined with And.
• Result: Marcus Webb and Dan Okafor.

Caution: Mixing && and || without parentheses is a classic bug. A && B || C does not mean what most people assume. Always parenthesise: (A && B) || C.

Check out Update and UpdateIf in Power Apps

How to Use the Search Function

Now I want a search box where the user types any fragment of a name or job title and the gallery narrows down as they type.

1. Insert a modern Text input control and rename it txtSearch.
2. Set its Placeholder property to "Search employees...".
3. Select the gallery and set Items to:

Search(
    Employees,
    txtSearch.Text,FullName,JobTitle
)

How does this formula work?

• Employees is the table.
• txtSearch.Text is the search string, read live from the text box, so results update on every keystroke.
• "FullName" and "JobTitle" are the columns being scanned, passed as quoted strings.
• Search performs a case-insensitive “contains” match. Typing ana returns Ana Ruiz and also the analysts, because “ana” appears inside “Analyst”.
• If txtSearch.Text is empty, Search returns the entire table. This is deliberate and extremely convenient, because you get a full unfiltered gallery by default with no extra If() wrapper.

Here is an example you can see in the screenshot below:

Combining Search with Filter

You almost always want both. Search handles the free text, Filter handles the hard rules:

Search(
    Filter(Employees, IsActive = true),
    txtSearch.Text,FullName, JobTitle
)

• The inner Filter runs first, removing inactive employees.
• The outer Search then does the substring match on what survived.
• Dan Okafor never appears, no matter what the user types, because he was removed before Search ever saw him.

Pro Tip: Put Filter on the inside and Search on the outside. Nesting the more restrictive condition innermost keeps the working set smaller.

Read Power Apps Weekday Function

How to Use the LookUp Function

Now I want to display a single employee’s email in a label, and separately grab a full record for a Patch operation.

1. Insert a Label onto the screen.
2. Set its Text property to:

LookUp(Employees, FullName = "Ana Ruiz", Email)

How does this formula work?

• Employees is the table.
• FullName = "Ana Ruiz" is the condition. LookUp scans top to bottom and stops at the first true result.
• Email is the reduction formula. Because I supplied it, LookUp returns the text value ana@contoso.com rather than the whole record.
• Without that third argument, LookUp would return the entire record, and dropping a record into a Text property throws a type error.

The label displays ana@contoso.com.

LookUp returning a full record

Set(
    varSelectedEmployee,
    LookUp(Employees, ID = Gallery1.Selected.ID)
)

• No third argument, so the whole record comes back.
• Set stores it in a global variable you can reference anywhere, for example varSelectedEmployee.Salary.
• This is the standard pattern for passing a record between screens or into a Patch.

LookUp against a related list

LookUp(
    Departments,
    DeptName = ThisItem.Department,
    ManagerEmail
)

Inside a gallery, ThisItem.Department gives the current row’s department, and LookUp fetches the matching manager email from a second list. This is how you fake a join in Power Apps.

Caution: If no row matches, LookUp returns blank, not an error. LookUp(Employees, FullName = "Nobody", Email) gives you an empty string and your app carries on silently. Wrap it when it matters: Coalesce(LookUp(Employees, FullName = "Nobody", Email), "Not found").

Read Power Apps Refresh Function

Filter vs Search vs LookUp: Side-by-Side Comparison

	Filter	Search	LookUp
Returns	Table (0 to many rows)	Table (0 to many rows)	Single record, or single value
Match type	Exact, as defined by your condition	Case-insensitive “contains”	Exact, as defined by your condition
Column types supported	Any: text, number, date, boolean, choice	Text columns only	Any
How columns are named	Bare identifier: Department	Quoted string: "Department"	Bare identifier: Department
Empty input behaviour	Returns rows matching the condition	Blank search string returns all rows	Returns blank
Stops early	No, scans everything	No	Yes, at first match
Typical property	Gallery Items, Data table Items	Gallery Items	Label Text, Default, variables
Multiple conditions	Yes, unlimited	No, one search string only	Yes

One row in this table matters more than the rest: Returns. Filter and Search give you a table. LookUp gives you a record. Almost every “invalid argument type” red squiggle in Power Apps traces back to a maker ignoring that line.

Filter vs LookUp

They take the same shape of condition, so people use them interchangeably. They should not.

• Use Filter when the answer is a list. Gallery items, data table items, a collection you are about to loop over with ForAll.
• Use LookUp when the answer is one thing. A price, an email, a record to patch.
• First(Filter(Employees, ID = 5)) and LookUp(Employees, ID = 5) produce the same record, but LookUp is more readable and it stops scanning once it finds the match.
• LookUp is not meaningfully faster on delegated data sources, because the server does the work either way. The readability win is the real win.

Decision rule: if you would ever write First() around your Filter, you wanted LookUp.

Filter vs Search

• Filter requires you to write the matching logic yourself. If you want “contains”, you write "..." in Column or StartsWith(Column, "...").
• Search has “contains” baked in and can span several columns with one call.
• Search cannot do >, <, dates, booleans, or choice columns. At all.
• Search’s blank-string-returns-everything behaviour saves you an If(IsBlank(...)) wrapper. Replicating that with Filter takes real effort.

The Filter equivalent of my earlier Search example is genuinely uglier:

Filter(
    Employees,
    IsBlank(txtSearch.Text)
    || txtSearch.Text in FullName
    || txtSearch.Text in JobTitle
)

That works, but the in operator is not delegable on SharePoint, so it will only ever look at your first 500 or 2,000 rows.

Search vs LookUp

These two rarely compete, because their outputs are so different. The only real overlap is “the user typed something and I want the matching record”.

• If the user typed a partial value and you want to show them the options, use Search into a gallery.
• If the user picked from that gallery, use LookUp on the ID to get the definitive record.

That two-step pattern, Search to narrow then LookUp to resolve, is how most well-built canvas apps handle record selection.

Check out Search a SharePoint List in Power Apps

Delegation and Performance Considerations

This is where the three functions really diverge, and where apps quietly break at row 501.

When Power Apps cannot push your query to the server, it downloads only the first N rows (default 500, configurable up to 2,000 in Settings → General → Data row limit) and filters locally. You get correct-looking results on test data and wrong results in production.

Filter delegation

Filter is the most delegable of the three. On a SharePoint list, these operators delegate cleanly: =, <>, >, >=, <, <=, And / &&, Or / ||, Not / !, StartsWith, TrimEnds, and IsBlank.

These do not delegate on SharePoint: in used as a substring operator, Len, Search, Sum, Average, Concat, and any comparison where both sides are columns.

So this delegates:

Filter(Employees, StartsWith(FullName, txtSearch.Text))

And this does not:

Filter(Employees, txtSearch.Text in FullName)

The second one is the most common delegation mistake in Power Apps. It looks harmless, it is a one-word change from the first, and it silently caps your app at the row limit.

Search delegation

Search is not delegable on SharePoint. It is delegable on Dataverse and on SQL Server, where it translates into a server-side “contains” query.

This is the detail that surprises people most. Search is the most user-friendly of the three functions and the least usable at scale on the most common data source in the Power Platform.

Data source	Filter	Search	LookUp
SharePoint	Partial	No	Partial
Dataverse	Yes	Yes	Yes
SQL Server	Yes	Yes	Yes
Excel in OneDrive	No	No	No
Collections (in memory)	No limit applies	No limit applies	No limit applies

Note: SQL Server and Dataverse both require a premium licence. If Search-at-scale is your requirement, that licence cost is part of the design decision, not an afterthought.

LookUp delegation

LookUp delegates on the same operators as Filter, on the same data sources. There is one nuance worth knowing: even when LookUp is delegable, Power Apps issues a query and takes the first returned row. It does not magically become instant. Index the column you are searching on in SQL Server or Dataverse if the table is large.

Practical workarounds

Here is what I actually do when the delegation warning appears:

1. Swap in for StartsWith. You lose mid-word matching, you gain a delegable query. Most users type from the beginning of a name anyway.
2. Filter server-side first, then Search locally. If Filter(Employees, Department = "Finance") reduces the set to under 2,000 rows, the non-delegable Search wrapped around it is now operating on a complete set and is safe.
3. Move the data. If free-text search across tens of thousands of rows is a core requirement, SharePoint is the wrong backend. Move to Dataverse.
4. Load once into a collection on OnStart — only for genuinely small reference tables such as departments, categories, or statuses. Never for transactional data.
5. Raise the data row limit to 2,000 as a last resort, and understand that you have bought headroom, not a fix. It also slows initial load.
6. Use Concurrent() in OnStart when you are loading several reference collections, so they fetch in parallel rather than in sequence.

Caution: Never treat the blue delegation warning as cosmetic. It is not a style suggestion. It is Power Apps telling you your results will be wrong once the table grows.

Common Errors and How to Fix Them

Error message	Why it happens	The fix
“Invalid argument type. Expecting a Record value, but of a different type.”	You passed a Filter result (a table) where a record was expected, usually in Patch or a Text property	Use LookUp instead, or wrap with First()
“The function ‘Search’ has some invalid arguments.”	Column names passed as identifiers instead of quoted strings	Change FullName to "FullName"
“Delegation warning. The highlighted part of this formula might not work correctly on large data sets.”	Non-delegable operator or function against a server data source	Swap in for StartsWith, or reduce the set with a delegable Filter first
Gallery is completely empty	The Search column does not exist, or the condition never evaluates true	Check the exact internal column names in SharePoint, not the display names
“Name isn’t valid. ‘Department’ isn’t recognized.”	Column renamed in the data source, or the connection was not refreshed	Select the data source in the Data pane and choose Refresh
LookUp returns blank instead of a value	No matching row, or you compared a text column to a number	Wrap in Coalesce(LookUp(...), "Not found") and check data types
“Incompatible type. We can’t evaluate your formula because the column types are incompatible.”	Comparing a SharePoint Choice column directly to text	Use Department.Value = "Finance"
Search works in Studio but not in the published app	Row limit reached, non-delegable Search over 500+ rows	Move to Dataverse or pre-filter delegably

Pro Tip: SharePoint internal column names are not the display names. A column created as “Job Title” has an internal name of Job_x0020_Title. Power Apps usually handles the translation, but if a Search silently returns nothing, this is the first thing to check.

Things to Keep in Mind

• All three are case-insensitive by default on text comparisons in SharePoint and Dataverse. SQL Server depends on the database collation, so behaviour can differ between environments.
• Search only works on text columns. Choice, Lookup, Person, Number, and Date columns are invisible to it. To search a Choice column, use Filter with Department.Value = "...".
• Blank and empty string are not the same. IsBlank("") returns false. Use IsBlankOrError() for values and IsEmpty() for tables when validating input.
• ThisItem vs ThisRecord. Inside a gallery, use ThisItem. Inside Filter, ForAll, or Sort scope, use ThisRecord to disambiguate when column names collide between two tables.
• Choice columns need .Value. This is the most common silent failure in Filter conditions against SharePoint.
• Search returns everything on a blank string. Useful, but it means your gallery loads the full table on screen open. On a large list that is a slow first paint.
• Premium licensing applies to SQL Server, Dataverse, and custom connectors. SharePoint and Excel are standard connectors.
• Modern controls behave the same as classic controls for all three functions. The difference is styling and theming, not data logic.
• Test with real volume. Duplicate your list to 3,000+ rows before you ship. Delegation bugs do not exist at 20 rows.
• Republish after formula changes. Editing in Studio does not update the published app until you select Publish.

Frequently Asked Questions

In this article, I covered what the Power Apps Filter, Search, and LookUp each return, how to use all three against a single SharePoint list, how they compare directly, and the delegation behaviour that decides which one you can actually ship. The short version stays true: many rows means Filter, a search box means Search, one record means LookUp.

I hope you found this tutorial helpful. If you have any questions, let me know in the comments section.

You may also like the following tutorials:

• Power Apps Dropdown Show Only Unique Values
• Power Apps CountRows Function