#PowerPlatformTip 63: 'Mastering the Lookup Function'

TL;DR: LookUp(Source, Condition, Result) and LookUp(Source, Condition).Column return the same value. With Explicit Column Selection (on by default in new apps) Power Apps requests only the columns your formulas reference in both cases, so pick the form by readability. The third argument shines when you need a calculated value, and With when you need several columns from one match.

💡 Challenge

LookUp has an optional third argument, and many makers wonder whether LookUp(Source, Condition, Column) is faster than LookUp(Source, Condition).Column. The bigger risks are elsewhere: a condition that isn’t delegated to SharePoint or Dataverse, and a record stored in a variable that silently misses columns.

✅ Solution

Know what each form does. Without the third argument, LookUp returns the full first matching record. With it, the result formula is evaluated on that record and reduces it to one value. Either way, the condition decides delegation, and Explicit Column Selection decides which columns are retrieved.

🔧 How It’s Done

1. Use the result argument for one value

🔸 LookUp(Employees, Email = User().Email, Department) returns the department text directly.

🔸 The result argument is a formula, not only a column name: LookUp(IceCream, Quantity > 150, Quantity + OnOrder) returns the sum for the first match.

2. The dot syntax returns the same value

🔸 LookUp(Employees, Email = User().Email).Department finds the same record and returns the same text.

🔸 With Explicit Column Selection on, Power Apps analyses your formulas and retrieves only the columns your app uses, for both forms. Choose the form that reads better in context.

3. Watch out when you store the record

🔸 Set(varMe, LookUp(Employees, Email = User().Email)) stores a record. Columns that no formula in the app references may not be retrieved, so varMe.SomeColumn can be blank although the row has a value. Reference the column in a formula (or store only the value you need with the result argument) instead of relying on the full record.

4. Mind delegation on remote data sources

🔸 Against a SharePoint list or Dataverse table, LookUp’s condition is delegated only when its functions and operators are delegable for that data source. If not, Power Apps evaluates the condition only against the first 500 rows (configurable up to 2,000) and shows a delegation warning. A LookUp that works in testing with a small table can return blank or the wrong record once the table grows. Check the delegation list for your data source.

5. Fetching several columns from the same match

🔸 Don’t call LookUp once per column, that repeats the same search. Wrap it in With({rec: LookUp(Source, Condition)}, ...) and use rec.Column1, rec.Column2 inside the With scope: one search, several values.

🎉 Result

Your lookups return the right value with one search per record, stay correct as the data source grows past the delegation limit, and don’t break because a stored record lacks a column.

🌟 Key Advantages

🔸 Clear choice between the two forms, based on readability

🔸 Calculated results in one call with the result argument

🔸 One search for several columns with With

🔸 No surprises from blank columns in stored records

🧪 The Three Forms Side by Side

Tested on a table p with Anna (31, Bern), Ben (45, Zurich) and Cara (22, Basel), and on a Dataverse Accounts table.

🔸 LookUp(p, Age > 30, Name), LookUp(p, Age > 30).Name and With({r: LookUp(p, Age > 30)}, r.Name) all return "Anna": same value, same first match. On Dataverse, LookUp(Accounts, 'Account Number' = "ZZ-C", 'Account Name') and LookUp(Accounts, 'Account Number' = "ZZ-C").'Account Name' also return the same text.

🔸 With pays off for several columns: With({r: LookUp(p, Age > 30)}, r.Name & " / " & r.City) returns "Anna / Bern" from one search.

🔸 No match: all three forms return Blank(). LookUp(p, Age > 100, Name) is blank, LookUp(p, Age > 100).Name is blank, and IsBlank(LookUp(p, Age > 100)) is true. Give a default with Coalesce(LookUp(p, Age > 100, Name), "nobody"), which returns "nobody".

🔸 The third argument is a formula, not only a column name. LookUp(p, Age > 30, Name & " (" & Age & ")") returns "Anna (31)", and LookUp(p, Age > 30, {N: Name, C: City}) returns the record {N:"Anna", C:"Bern"}.

🔸 The third argument is evaluated only on a match: LookUp(p, Age > 100, "none") returns Blank(), not "none".

🔸 First(Filter(p, Age > 30)).Name returns "Anna" as well.

🛠️ FAQ

1. What is the difference between LookUp(Source, Condition, COLUMN) and LookUp(Source, Condition).COLUMN?

The first evaluates the result formula on the first matching record, the second returns the record and then reads one column from it. Both return the same value, both return Blank() when nothing matches, and with Explicit Column Selection both retrieve only the columns your app references.

2. When should I use the result argument?

When you need one value or a calculated value from the match, and when you want to store just that value in a variable instead of the whole record.

3. Can I always rely on LookUp(Source, Condition, COLUMN)?

It only returns the first match, in the order of the data source. If several records can match, sort first or use Filter to get all of them.

4. How do I return a default value when LookUp finds nothing?

Wrap it: Coalesce(LookUp(Source, Condition, Column), "default"). Putting the default in the third argument does not work, LookUp(p, Age > 100, "none") returns Blank() because the third argument is only evaluated for a matching record.

5. Can the third argument combine several columns?

Yes. It is a formula evaluated on the matching record: LookUp(p, Age > 30, Name & " (" & Age & ")") returns "Anna (31)", and a record {N: Name, C: City} returns both values in one call.

🔗 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.