How to use LookupRows in AMPscript (and when to switch to Lookup or LookupOrderedRows)

When a subscriber can have several related rows in a Data Extension (orders, courses, appointments), you need to fetch all of them and build a list inside the email. That is what LookupRows is for.

The three lookup functions

In all three, the first parameter is the Data Extension name, not its external key.

LookupRows syntax

LookupRows("DataExtensionName", "Column1", "Value1" [, "Column2", "Value2", ...])

Each column/value pair is an equality filter, and the pairs are combined with "AND".

Example: the subscriber's orders

The Orders Data Extension has the columns Email, Number, Amount and Status.

%%[
SET @email  = AttributeValue("EmailAddress")
SET @orders = LookupRows("Orders", "Email", @email, "Status", "Shipped")
SET @total  = RowCount(@orders)

IF @total > 0 THEN
]%%
<p>You have %%=v(@total)=%% order(s) on the way:</p>
<ul>
%%[
  FOR @i = 1 TO @total DO
    SET @row    = Row(@orders, @i)
    SET @number = Field(@row, "Number")
    SET @amount = Field(@row, "Amount")
]%%
  <li>Order %%=v(@number)=%%: $%%=FormatNumber(@amount, "N2", "en-US")=%%</li>
%%[
  NEXT @i
]%%
</ul>
%%[ ELSE ]%%
<p>No orders on the way right now.</p>
%%[ ENDIF ]%%

The pieces:

• RowCount(@rowset) tells you how many rows came back;
• Row(@rowset, @i) gets row number @i (counting starts at 1);
• Field(@row, "Column") reads a column from that row.

Sort and limit with LookupOrderedRows

To show only the 3 most recent orders, switch to LookupOrderedRows, which takes the row count and the sort order before the filters:

%%[
SET @latest = LookupOrderedRows("Orders", 3, "OrderDate DESC", "Email", @email)
SET @total  = RowCount(@latest)
]%%

The sort order accepts more than one column, separated by commas: "OrderDate DESC, Number ASC".

Performance and limits

• LookupRows returns at most 2,000 rows. If you need more than that, an email is not the right place for the list.
• Every lookup function runs once per subscriber in the send. In large sends, avoid repeating the same lookup: store the result in a variable and reuse it.
• Filtering on columns that are part of the Data Extension's primary key is usually faster.

Summary

Use Lookup for a single value, LookupRows for several rows and LookupOrderedRows when order or limit matters. Check RowCount before looping through the result and remember the 2,000-row limit.