AMPscript Lookup: Lookup, LookupRows, and LookupOrderedRows in SFMC

Personalization Strings cover the simple cases. AMPscript is what you reach for when the email needs to do more than inject a single value, and the first AMPscript function most developers meet is Lookup — the function that retrieves data from another Data Extension at send time. This guide covers the AMPscript Lookup family end to end: Lookup for a single value, LookupRows and LookupOrderedRows to retrieve multiple rows, the syntax that silently fails, and the two conditional and formatting patterns you almost always pair with a lookup.

Table of Contents

1. What an AMPscript Lookup actually does
2. LookupRows: retrieving multiple rows
3. LookupOrderedRows and Lookup vs LookupRows
4. Tiered conditional content
5. Formatting values for display
6. Common AMPscript Lookup errors

Every AMPscript block is wrapped in %%[ ... ]%%, and the output of a variable comes from %%=v(@varName)=%%.

Pattern 1: The AMPscript Lookup() function across Data Extensions

The AMPscript Lookup() function is the one you reach for first, because the sendable DE usually doesn't hold every attribute the email needs. A common case: the email sends from each subscriber's assigned Sales Rep, but the rep's name, email, and phone live in a separate SalesRep_DE, not duplicated into the sendable.

Customer_DE (sendable):
  - CustomerID     <- Subscriber Key
  - EmailAddress
  - SalesRepID     <- just an ID

SalesRep_DE (non-sendable):
  - SalesRepID     <- Primary Key
  - RepName
  - RepEmail
  - RepPhone

Fetch the rep data at send time with Lookup():

%%[
VAR @repID, @repName, @repEmail, @repPhone

SET @repID    = AttributeValue("SalesRepID")
SET @repName  = Lookup("SalesRep_DE", "RepName",  "SalesRepID", @repID)
SET @repEmail = Lookup("SalesRep_DE", "RepEmail", "SalesRepID", @repID)
SET @repPhone = Lookup("SalesRep_DE", "RepPhone", "SalesRepID", @repID)

/* Always handle the not-found case */
IF EMPTY(@repName) THEN
  SET @repName  = "Customer Care Team"
  SET @repEmail = "support@company.com"
  SET @repPhone = "1800 xxxx"
ENDIF
]%%

<p>Your account manager:</p>
<p><strong>%%=v(@repName)=%%</strong></p>
<p>%%=v(@repEmail)=%% | %%=v(@repPhone)=%%</p>

Lookup syntax:

Lookup("TableName", "FieldToReturn", "FieldToMatch", ValueToMatch)

The pair that gets swapped constantly is positions 2 and 3 - FieldToReturn and FieldToMatch. Order wrong = Lookup silently returns empty. No error, just blank output.

/* wrong - argument positions swapped */
SET @repName = Lookup("SalesRep_DE", "SalesRepID", "RepName", @repID)

/* right */
SET @repName = Lookup("SalesRep_DE", "RepName", "SalesRepID", @repID)

LookupRows: retrieving multiple rows from a Data Extension

Lookup returns one value from one matching row. The moment you need every matching row — all of a customer's recent transactions, every item in an order, each entry in an engagement history — you reach for LookupRows instead. Where Lookup returns a single value, LookupRows returns a rowset: a collection of rows you can count and iterate through.

The syntax matches one column against a value and hands back the whole rowset:

%%[
VAR @rows, @rowCount, @i, @row, @product

SET @rows = LookupRows("Order_DE", "CustomerID", @customerID)
SET @rowCount = RowCount(@rows)

IF @rowCount > 0 THEN
  FOR @i = 1 TO @rowCount DO
    SET @row = Row(@rows, @i)
    SET @product = Field(@row, "ProductName")
]%%
    <p>%%=v(@product)=%%</p>
%%[
  NEXT @i
ENDIF
]%%

Three functions do the work together: LookupRows retrieves the rowset, RowCount tells you how many rows came back, and Row() plus Field() pull a single row and then a single column value out of it. The RowCount check is the LookupRows equivalent of the IF EMPTY discipline on a single Lookup — never iterate a rowset you have not confirmed has rows, or the email renders an empty block.

Common use cases for LookupRows:

• Retrieve all transactions for a customer and list them in the email
• Pull every saved preference from a profile Data Extension
• Build an order summary by iterating the order-line rows

LookupOrderedRows and when to use which lookup

LookupRows returns rows in no guaranteed order. When the order matters — the three most recent orders, the highest loyalty balances — use LookupOrderedRows, which adds a maximum row count and a sort:

SET @rows = LookupOrderedRows("Order_DE", 3, "OrderDate DESC", "CustomerID", @customerID)

That call retrieves at most three rows, sorted by OrderDate descending, so you get the customer's three latest orders ready to iterate exactly as above. Passing 0 as the row count returns all matching rows in the requested order.

Choosing between the lookup functions comes down to how many rows you need and whether order matters. Use Lookup when you need a single field from a single row, such as a rep name or a tier label. Use LookupRows when you need every matching row and order is irrelevant. Use LookupOrderedRows when you need a bounded, sorted set, which is also the safe default for large Data Extensions, because returning thousands of unbounded rows into an email is how a send slows to a crawl. (There is also LookupOrderedRows's single-value cousin, LookupValue, for the rare case you want one value with ordering applied — but for one value, plain Lookup is almost always what you want.)

Pattern 2: Tiered conditional content

Loyalty emails routinely say different things depending on the subscriber's current state:

%%[
VAR @points, @message, @pointsLeft

SET @points = AttributeValue("LoyaltyPoints")

IF @points >= 1000 THEN
  SET @message    = "You've reached GOLD tier!"
  SET @pointsLeft = 0
ELSEIF @points >= 500 THEN
  SET @message    = "You're at SILVER tier"
  SET @pointsLeft = Subtract(1000, @points)
ELSE
  SET @message    = "You're at STANDARD tier"
  SET @pointsLeft = Subtract(500, @points)
ENDIF
]%%

<p>%%=v(@message)=%%</p>
%%[IF @pointsLeft > 0 THEN]%%
  <p>Earn <strong>%%=v(@pointsLeft)=%%</strong> more points to reach the next tier!</p>
%%[ENDIF]%%

Two scripting blocks here: the top one sets variables, the inline IF decides whether to render a paragraph at all.

When conditional content is structural (show or hide whole blocks of HTML), either AMPscript inline IF or a Dynamic Content Block works. Dynamic Content is nicer for non-developers to maintain; AMPscript is nicer when logic is complex or needs lookups.

Pattern 3: Formatting values for display

Raw data rarely matches how humans read it. 1500000 in a DE should display as 1,500,000 VND in the email. 2024-01-15 should display as 15/01/2024.

%%[
VAR @today, @amount, @formattedAmount

SET @today = Format(Now(), "dd/MM/yyyy")
SET @amount = AttributeValue("OrderAmount")
SET @formattedAmount = Format(@amount, "N0")
]%%

Order date: %%=v(@today)=%%
Amount: %%=v(@formattedAmount)=%% VND

Format(value, "N0") = number with thousands separators, no decimals. Format(date, "dd/MM/yyyy") = day/month/year. Now() = current send time.

Other useful format strings:

• "yyyy-MM-dd" for ISO dates
• "hh:mm" for 12-hour time
• "HH:mm" for 24-hour time
• "C" for currency (locale-aware, use cautiously in international sends)

The three mistakes we still see

Mistake 1: No EMPTY() check after Lookup

Lookup returns empty when the match fails. Without an explicit fallback, the email just shows nothing where the data should be. Always:

SET @rep = Lookup("SalesRep_DE", "RepName", "SalesRepID", @repID)
IF EMPTY(@rep) THEN
  SET @rep = "Customer Care Team"
ENDIF

Mistake 2: Unclosed AMPscript blocks

Forgetting to close %%[ breaks rendering on the whole email. Test in Preview & Test immediately after writing AMPscript - if the Preview shows error text, the syntax is wrong and the email won't send.

Mistake 3: AMPscript in the Subject Line

Same rule as Personalization Strings: Subject Line does not render AMPscript. Move the logic upstream, pre-compute a field in the DE, and use a plain Personalization String in the subject.

Takeaway

AMPscript earns its place in the project when the email needs cross-DE data, conditional logic, or formatted values. The lookup family does most of the heavy lifting: Lookup with an IF EMPTY fallback for a single value, LookupRows with a RowCount check to retrieve multiple rows, and LookupOrderedRows when you need a bounded, sorted set. Pair them with IF/ELSEIF/ELSE for tiers and Format() for numbers and dates, and three or four patterns cover the overwhelming majority of real-world AMPscript. Preview & Test with multiple data profiles catches both logic bugs and syntax errors before they go to the full list.

---

Writing AMPscript for a production SFMC build? Our Salesforce team reviews and ships complex AMPscript on Marketing Cloud engagements. Get in touch ->

See our full platform services for the stack we cover.