sfmc.guide
Engagement Next
⌘K

LookupRows

Returns every matching row of a data extension as a rowset. Runtime-proven on a live Marketing Cloud Engagement CloudPage — including the variadic criteria and what a no-match returns.

Next
Runtime verified Test scripts included

Syntax

LookupRows(dataExt, searchColumn1, searchValue1[, searchColumnN, searchValueN, ...])  →  rowset

Parameters

Name Type Required Description
dataExt string Yes Name or external key of the data extension to read
searchColumn1 string Yes First column to filter on
searchValue1 string | number Yes Value the first column must equal
searchColumnN string No Further filter columns, each paired with a value
searchValueN string | number No Value the corresponding further column must equal

Example

%%[
  VAR @rows
  SET @rows = LookupRows("AMP_VERIFY_SCRATCH", "FirstName", "Alice")
]%%
Matches: %%=v(RowCount(@rows))=%% first Id: %%=v(Field(Row(@rows, 1), "Id"))=%%

With two Alice rows (A1, A3), renders Matches: 2 first Id: A1.

Because it returns every match, iterate the rowset with RowCount and Row:

%%[
  VAR @rows, @i
  SET @rows = LookupRows("AMP_VERIFY_SCRATCH", "FirstName", "Alice")
  FOR @i = 1 TO RowCount(@rows) DO
]%%
    Row %%=v(@i)=%%: %%=v(Field(Row(@rows, @i), "Id"))=%%
%%[ NEXT @i ]%%

Return value

rowset — one row for every row that matches all column/value pairs, in the data extension’s natural order.

A no-match returns an empty rowset, not a null or an error: RowCount(LookupRows("AMP_VERIFY_SCRATCH", "FirstName", "Zoltan")) rendered 0. Guard reads with RowCount — it never aborts.

Behaviour

Returns all matching rows, read 1-based. Each row is addressed by 1-based index with Row and its columns read with Field.

A no-match returns an empty rowset. RowCount on it is 0; no error is raised and the page does not abort.

Search criteria are variadic name/value pairs. Arguments after dataExt are read as column, value, column, value, …; every pair must match. Matching is case-insensitive — use LookupRowsCS for a case-sensitive comparison.

Show test script
%%[
  VAR @b, @rs, @rc, @i
  SET @b = RequestParameter("b")

  IF @b == "zzz" THEN
    OutputLine(Concat("CTRL=[alive]"))
  ENDIF

  IF @b == "safe" THEN
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "A1")
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "A2")
    DeleteData("AMP_VERIFY_SCRATCH", "Id", "A3")
    InsertData("AMP_VERIFY_SCRATCH", "Id", "A1", "FirstName", "Alice", "Score", 10)
    InsertData("AMP_VERIFY_SCRATCH", "Id", "A2", "FirstName", "Bob", "Score", 20)
    InsertData("AMP_VERIFY_SCRATCH", "Id", "A3", "FirstName", "Alice", "Score", 30)

    /* rowset of all matching rows, read 1-based */
    SET @rs = LookupRows("AMP_VERIFY_SCRATCH", "FirstName", "Alice")
    SET @rc = RowCount(@rs)
    OutputLine(Concat("LookupRows(FirstName=Alice) RowCount=[", @rc, "]"))
    FOR @i = 1 TO @rc DO
      OutputLine(Concat("  row ", @i, " Id=[", Field(Row(@rs, @i), "Id"), "] Score=[", Field(Row(@rs, @i), "Score"), "]"))
    NEXT @i

    /* a no-match returns an empty rowset with RowCount 0 */
    OutputLine(Concat("LookupRows nomatch RowCount=[", RowCount(LookupRows("AMP_VERIFY_SCRATCH", "FirstName", "Zoltan")), "]"))
  ENDIF
]%%

Availability

Platform Available
Marketing Cloud Engagement Yes
Marketing Cloud Next Check the official reference

See also