LookupRows

LookupRows is a data retrieval operation used in data processing, integration, and spreadsheet automation to fetch rows from a data set that satisfy specified criteria. It is commonly exposed as a function or built-in operation in ETL tools, scripting languages, and declarative data workflows. The operation typically accepts a source collection (such as a table, list, or array) and a set of one or more criteria or a key.

Behavior and scope: It can return zero, one, or many rows depending on the match, and may return multiple rows in a list or table form. Some implementations enforce exact-key matching; others support partial, range, or fuzzy matching. The function may support case sensitivity, data type coercion, and handling of nulls. When there are multiple matches, the result is often a multirow set that can be joined with other data or used for aggregation.

Usage scenarios: Commonly used to enrich data by looking up values from a reference table (for example, resolving a product code to a product name), to validate references, or to prepare data for join-like operations without performing a full join.

Examples: LookupRows(Products, ProductCode, "X123") returns all rows in Products where ProductCode equals "X123". More complex: LookupRows(Orders, {CustomerID: 987, Status: "Open"}).

Implementation and performance: Efficiency depends on how the source is indexed. In databases, this maps to indexed lookups or joins; in memory collections, it depends on the data structure. Large lookups may benefit from caching or precomputed maps.

Limitations: May return no rows if no match; may return multiple rows requiring de-duplication or aggregation; semantics differ across tools, so behavior should be checked in the respective documentation.