#PowerPlatformTip 63: 'Mastering the Lookup Function'

TL;DR: Use Lookup(Source, Condition, COLUMN) to return just the column value instead of Lookup(...).COLUMN, which fetches the whole record first.

💡 Challenge

Understanding the difference between Lookup(Source, Condition, COLUMN) and Lookup(Source, Condition).COLUMN, and optimizing your formulas for better performance. The gap widens further once the data source is a large SharePoint list or Dataverse table rather than an in-memory collection, where delegation determines whether your condition even runs where you think it does.

✅ Solution

Use the direct-column syntax Lookup(Source, Condition, COLUMN) to retrieve only the needed value and avoid the extra record retrieval step.

🔧 How It’s Done

1. Use direct-column syntax

🔸 Syntax: Lookup(Source, Condition, COLUMN)

🔸 Benefit: Returns only the column value, improving performance.

2. Avoid record-first syntax

🔸 Syntax: Lookup(Source, Condition).COLUMN

🔸 Drawback: Retrieves the entire record then extracts the column, which is less efficient.

3. Compare with an example

🔸 Lookup(MyCollection, Age > 30, Name) returns the name directly.

🔸 Lookup(MyCollection, Age > 30).Name finds the record then extracts the name.

4. Mind delegation on remote data sources

🔸 Against a SharePoint list or Dataverse table, Lookup’s condition is a delegable operation only for supported operators (equality, and most comparisons on simple column types). If your condition uses a non-delegable operator or function, in, nested Lookup/Filter calls, or certain text functions, Power Apps evaluates it only against the locally cached rows (up to the 500/2,000-row non-delegable limit) instead of the full table, and you’ll get the delegation warning banner in the formula bar. A Lookup that silently “works” in testing with a small dataset can start returning wrong results once the table grows past that limit in production.

5. Fetching several columns from the same match

🔸 Direct-column syntax only returns one column. If you need two or three fields from the same matching record, don’t call Lookup once per column, that repeats the same search each time. Wrap it in With({rec: Lookup(Source, Condition)}, ...) to fetch the record once and reference rec.Column1, rec.Column2, etc. inside the With scope, combining the direct-column syntax’s intent (search once) with access to multiple fields.

🎉 Result

By using the direct-column syntax, your app retrieves only what you need, leading to smoother performance and faster screen loads, and by respecting delegation limits, that performance holds up as your data source grows.

🌟 Key Advantages

🔸 Faster data retrieval by avoiding a full-record fetch

🔸 Simplified formulas that are easier to read

🔸 Reduced network and memory usage in your app

🔸 Combined with With, you get the same “search once” efficiency even when you need multiple columns back

🛠️ FAQ

Q: What is the difference between Lookup(Source, Condition, COLUMN) and Lookup(Source, Condition).COLUMN?

The first returns the column value directly from the first matching record, while the second fetches the entire record then extracts the column, adding an extra step.

Q: When should I use the direct-column syntax?

Use it whenever you only need a single value from the matching record to improve performance and reduce data transfer.

Q: Can I always rely on Lookup(Source, Condition, COLUMN)?

Yes, but it only returns the first match. If you need multiple records or more complex logic, consider using Filter or other functions accordingly.

🔗 Related Tips

🔸 #PowerPlatformTip 64: ‘Comparing Lookup vs. First(Filter)’ - see why Lookup and First(Filter()) perform identically despite the syntax difference.

🔸 Power Apps ComboBox DefaultSelectedItems - a practical case where the direct-column Lookup syntax simplifies a default-value formula.