I have used the lookup function and yes, it works fine. But, and hopefully am am overlooking something, I can’t find a way to return any other value than the display column value. I can change the display column, but I need to be able to return more than one value (column) after finding a record. In vlookup, you can reference the column, but in lookup, I can’t find a comparable parameter. Is this a limitation, or did I miss something?
You can use the dot operator to project values for a column. For example
=Tasks.Lookup(AssignedTo, thisRow).TasksColumn will find all rows in the Tasks table where the AssignedTo matches the current row, and then projects the TasksColumn property of those rows.
Does that help?
@joost_mineur This post below (“Players and Leagues” doc) has helped me to conceptualize how lookup works and dispelled the doubts regarding as to how I should use it. I think it’ll help as I had encountered the same problem as you have.
Thank you both for your feedback - this answered my question. It works and it’s very easy to use (once you know…).
Funny to see how you can write it two ways: “Tasks.Lookup(AssignedTo, thisRow).TasksColumn” returns the same values as “Lookup(Tasks,AssignedTo, thisRow).TasksColumn”. It kind of worries me that I could not find this myself looking through the documentation (might very well be me, I am (very) old school). But I am glad to find out it can be done so easily.
For what it is worth to other newbe’s: I would never want to rely on whatever is marked as display column - it seems easy to do so, but the display column can be changed and all of your formula’s that depend on this column would fall apart.