LookupRows()

Returns an unordered rowset from a data extension. The function can return a rowset with up to 2,000 rows. This function is case-insensitive.

AMPscript includes several variations of the LookupRows() function.

• The LookupRowsCS() function is a case-sensitive version of this function.
• The LookupOrderedRows() function allows you to specify a column and direction to sort the rowset by.
• The LookupOrderedRowsCS() function is a case-sensitive version of LookupOrderedRows().

LookupRows(dataExt,
           searchColumn1, searchValue1,
           [searchColumn2, searchValue2...])

The LookupRows() function has three parameters.

• dataExt (string): Required. The name of the data extension that contains the data that you want to retrieve.
• searchColumn1 (string): Required. The name of the column to search. This value is case-insensitive.
• searchValue1 (string): Required. The value in the specified column that identifies the rows to retrieve. This value is case-insensitive.

You can optionally append additional search columns and values to the end of the parameter string.

This example uses a data extension called "MembershipRewardsProgramme," which contains the data in this table.

This code retrieves a list of members where the value of the Area column is "Chelsea".

<p>Members in Chelsea:</p>
<ul>
%%[
  Var @membersChelsea, @rowCount

  /* Retrieve rows */
  /* Note that case of data extension, column name, and value don't match the source data */
  Set @membersChelsea = LookupRows("membershiprewardsprogramme","area","chelsea")
  Set @rowCount = RowCount(@membersChelsea)

  If @rowCount > 0 then
    For @counter = 1 to @rowCount do
      Var @row, @memberId, @firstName, @surname, @rewardsPoints
      Set @row = Row(@membersChelsea, @counter)
      Set @memberId = Field(@row, "MemberId")
      Set @firstName = Field(@row, "FirstName")
      Set @surname = Field(@row, "Surname")
      Set @rewardsPoints = Field(@row, "RewardsPoints")
]%%
  <li>%%=v(@firstName)=%% %%=v(@surname)=%% (member ID %%=v(@memberId)=%%) - Point balance: %%=v(@rewardsPoints)=%%</li>
%%[
      Next @counter
  EndIf
]%%
</ul>

The code example outputs an unordered list of results.

Members in Chelsea:

  • Dennis Smithers (member ID 1) - Point balance: 92374
  • Jian Yeh (member ID 5) - Point balance: 15123

• LookupRowsCS()