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().

1LookupRows(dataExt,
2           searchColumn1, searchValue1,
3           [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”.

1<p>Members in Chelsea:</p>
2<ul>
3%%[
4  Var @membersChelsea, @rowCount
5
6  /* Retrieve rows */
7  /* Note that case of data extension, column name, and value don't match the source data */
8  Set @membersChelsea = LookupRows("membershiprewardsprogramme","area","chelsea")
9  Set @rowCount = RowCount(@membersChelsea)
10
11  If @rowCount > 0 then
12    For @counter = 1 to @rowCount do
13      Var @row, @memberId, @firstName, @surname, @rewardsPoints
14      Set @row = Row(@membersChelsea, @counter)
15      Set @memberId = Field(@row, "MemberId")
16      Set @firstName = Field(@row, "FirstName")
17      Set @surname = Field(@row, "Surname")
18      Set @rewardsPoints = Field(@row, "RewardsPoints")
19]%%
20  <li>%%=v(@firstName)=%% %%=v(@surname)=%% (member ID %%=v(@memberId)=%%) - Point balance: %%=v(@rewardsPoints)=%%</li>
21%%[
22      Next @counter
23  EndIf
24]%%
25</ul>

The code example outputs an unordered list of results.

1Members in Chelsea:
2
3  • Dennis Smithers (member ID 1) - Point balance: 92374
4  • Jian Yeh (member ID 5) - Point balance: 15123

• LookupRowsCS()