Difference between Lookup vs LookupRows vs LookupOrderedRows
AMPscript provides multiple functions for retrieving data from Data Extensions (DEs) in Salesforce Marketing Cloud (SFMC). The three most commonly used functions are Lookup, LookupRows, and LookupOrderedRows.
Function | Returns | Use Case | Best For |
Lookup | A single value (field) from the first matching row | When you need just one field from a single row | Fetching a specific piece of data, like an email address |
LookupRows | A rowset (multiple rows) with all columns | When you need multiple rows but don’t care about sorting | Fetching all orders of a customer |
LookupOrderedRows | A rowset (multiple rows) with sorting | When you need multiple rows in a specific order | Fetching the latest orders by date |
Lookup Function
- Fetches one field value from the first matching row in the Data Extension.
- Returns only one value (not multiple rows).
Syntax:
%%=Lookup(“DataExtensionName”, “ColumnToRetrieve”, “ColumnToMatch”, “Value”)=%%
Example:
Retrieve the first name of a subscriber whose email is john@example.com.
%%=Lookup(“Customers”, “FirstName”, “Email”, “john@example.com”)=%%
Best When: You need just one field from a single row.
LookupRows Function
- Retrieves multiple rows (but doesn’t sort them).
- Returns a rowset (you can loop through it to access multiple rows).
- Useful when you don’t need sorting.
Syntax:
SET @rows = LookupRows(“DataExtensionName”, “ColumnToMatch”, “Value”)
Example:
Get all orders for a specific customer ID.
SET @orderRows = LookupRows(“Orders”, “CustomerID”, “12345”)
To loop through the results:
FOR @i = 1 TO RowCount(@orderRows) DO
SET @row = Row(@orderRows, @i)
SET @orderID = Field(@row, “OrderID”)
SET @orderDate = Field(@row, “OrderDate”)
OUTPUT(CONCAT(“<br>Order ID: “, @orderID, “, Date: “, @orderDate))
NEXT
Best When: You need multiple rows, but order doesn’t matter.
LookupOrderedRows Function
- Retrieves multiple rows but with sorting.
- Allows filtering and ordering (ascending/descending).
- Useful when you need the latest or earliest records.
Syntax:
SET @rows = LookupOrderedRows(“DataExtensionName”, NumberOfRows, “SortField ASC/DESC”, “ColumnToMatch”, “Value”)
Example:
Get latest 5 orders for a specific customer, sorted by order date (latest first).
SET @latestOrders = LookupOrderedRows(“Orders”, 5, “OrderDate DESC”, “CustomerID”, “12345”)
To loop through the results:
FOR @i = 1 TO RowCount(@latestOrders) DO
SET @row = Row(@latestOrders, @i)
SET @orderID = Field(@row, “OrderID”)
SET @orderDate = Field(@row, “OrderDate”)
OUTPUT(CONCAT(“<br>Latest Order ID: “, @orderID, “, Date: “, @orderDate))
NEXT
Best When: You need multiple rows sorted by date or priority
Key Takeaways
Function | Returns | Supports Multiple Rows? | Supports Sorting? | Use Case |
Lookup | A single field value |
|
| Fetching one specific field (e.g., getting a customer’s first name) |
LookupRows | Multiple rows (rowset) |
|
| Retrieving all orders for a customer (unordered) |
LookupOrderedRows | Multiple rows (rowset) |
|
| Fetching the latest 5 transactions for a user (ordered by date) |
No
YesWhen to Use Which Function?
- Use Lookup → When you need only one field from the first matching row.
- Use LookupRows → When you need multiple rows but don’t care about order.
- Use LookupOrderedRows → When you need multiple rows sorted (e.g., latest orders first).
