sfmc.guide
Engagement Next
⌘K

Lookup

Returns a single field value from the first matching row of a data extension. Runtime-proven on a live Marketing Cloud Engagement CloudPage — including the variadic name/value criteria and what a no-match returns.

Next
Runtime verified Test scripts included

Syntax

Lookup(dataObject, returnColumn, searchColumn1, searchValue1[, searchColumnN, searchValueN, ...])  →  string

Parameters

Name Type Required Description
dataObject string Yes Name or external key of the data extension to read
returnColumn string Yes Column whose value is returned from the first matching row
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 @name
  SET @name = Lookup("AMP_VERIFY_SCRATCH", "FirstName", "Id", "A2")
]%%
Name: %%=v(@name)=%%

With a row whose Id is A2 and FirstName is Bob, renders Name: Bob.

Because the search accepts extra column/value pairs, a single call can filter on a composite criterion — here the one Alice row whose Score is 30:

%%[
  VAR @id
  SET @id = Lookup("AMP_VERIFY_SCRATCH", "Id", "FirstName", "Alice", "Score", 30)
]%%

Returns A3 — the second Alice row — not the first Alice row whose Score is 10.

Return value

string — the value of returnColumn from the first row that matches every column/value pair.

A no-match returns an empty string, not a null or an error: Lookup("AMP_VERIFY_SCRATCH", "FirstName", "Id", "NOPE") rendered nothing. There is no sentinel to test for — branch on Empty() or RowCount(LookupRows(...)) when you need to distinguish absence.

Behaviour

Returns a scalar from the first matching row. When several rows match, only the first row’s returnColumn is returned; use LookupRows to read them all.

A no-match returns an empty string. No error is raised and the page does not abort — the call simply yields nothing.

Search criteria are variadic name/value pairs. Arguments after returnColumn are read as column, value, column, value, …; every pair must match for a row to qualify. Numeric criteria ("Score", 30) are accepted as numbers.

Show test script
%%[
  VAR @b
  SET @b = RequestParameter("b")

  /* control branch: proves the page itself renders */
  IF @b == "zzz" THEN
    OutputLine(Concat("CTRL=[alive]"))
  ENDIF

  IF @b == "safe" THEN
    /* seed deterministic rows */
    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)

    /* scalar return from the first matching row */
    OutputLine(Concat("Lookup A2.FirstName=[", Lookup("AMP_VERIFY_SCRATCH", "FirstName", "Id", "A2"), "]"))
    OutputLine(Concat("Lookup A3.Score=[", Lookup("AMP_VERIFY_SCRATCH", "Score", "Id", "A3"), "]"))
    /* a no-match returns an empty string */
    OutputLine(Concat("Lookup nomatch=[", Lookup("AMP_VERIFY_SCRATCH", "FirstName", "Id", "NOPE"), "]"))
    /* variadic two-pair criteria */
    OutputLine(Concat("Lookup pair.Id=[", Lookup("AMP_VERIFY_SCRATCH", "Id", "FirstName", "Alice", "Score", 30), "]"))
  ENDIF
]%%

Availability

Platform Available
Marketing Cloud Engagement Yes
Marketing Cloud Next Yes, from API 67.0

See also