How to use Salesforce Marketing Cloud (SFMC) LookupOrderedRows AMPScript to search data in a data extension that returns multiple rows

Today, we will talk about Salesforce Marketing Cloud (SFMC) and AMPScript (LookupOrderedRows AMPScript to be specific). Salesforce Marketing Cloud (SFMC) is a customer relationship management (CRM) platform for marketers that allows marketers to create 1-to-1 journeys across the entire Salesforce Customer Success Platform -- journeys that span not only digital marketing, but sales and service as well. This simplifies data management, segmentation and campaign management, making it easy to create compelling digital marketing campaigns, boost customer engagement and ultimately drive more leads into Salesforce. (source: Salesforce.com)

In this tutorial, we will go over the LookupOrderedRows AMPScript that can be used to search data in a data extension that returns multiple rows.

What is LookupOrderedRows AMPScript?

It returns a specified number of rows. You can specify multiple additional field/value pairs as part of an AND clause.

Syntax

LookupOrderedRows(dataExtension, count, order, column, value)

Function Properties

Source:  Salesforce Marketing Cloud Developer Documentation

Example

Let's say you have a data extension in SFMC named as "EmployeeRecords_DataExtension" with the following columns:

- Email Address

- First Name

- Last Name

- State

For this example, let's assume that we need to search employee records (in EmployeeRecords_DataExtension) and find out which employees are from "Texas" state.

In the AMPScript, we will use "Texas" as our search term and we will perform a LookupOrderedRows search for "Texas" in State column of the EmployeeRecords_DataExtension.

LookupOrderedRows AMPScript to search data in a data extension that returns multiple rows

%%[

var @searchtermlookupValue
set @searchtermlookupValue = "Texas" /* this will be our search term */

var @ReturnRows
set @ReturnRows = 0 /* Use 0 to get all results */

var @GetRows
set @GetRows = LookupOrderedRows("EmployeeRecords_DataExtension",@ReturnRows,"First_Name asc","State", @searchtermlookupValue) /* syntax of LookupOrderedRows */

var @GetRow, @GetRowCount
set @GetRowCount = rowcount(@GetRows)

if @GetRowCount > 0 then /* if search term or criteria matches with the data in the data extension then we will get 1 or more rows*/

var @GetRowNumber
for @GetRowNumber = 1 to @GetRowCount do /* FOR LOOP for each row */

set @GetRow = row(@GetRows,@GetRowNumber) /* calculate row number */

var @First_Name_Column
set @First_Name_Column = field(@GetRow,"First_Name") /* display value of the column from data extension */

var @Last_Name_Column
set @Last_Name_Column = field(@GetRow,"Last_Name") /* display value of the column from data extension */

var @Email_Address_Column
set @Email_Address_Column = field(@GetRow,"Email_Address") /* display value of the column from data extension */

]%%

<!-- Use this for row number: %%=v(@GetRowNumber)=%% Row -->
First Name is %%=v(@First_Name_Column)=%% |  Last Name is %%=v(@Last_Name_Column)=%% |  Email is %%=v(@Email_Address_Column)=%%
<br/>
<hr/>


%%[

next @i ]%%

%%[ else ]%%

<!-- If search term does not match with the data for any record in data extension -->
---- No rows found ----

%%[ endif ]%%

Note 1: I have not tested the above AMPScript. The above code / AMPScript is for learning purpose only. Please test it before using it on production.

Note 2: I do not take any credit for the above code / AMPScript. The above code / AMPScript is based on what I have learned from the resources available on Salesforce Marketing Cloud Developer Documentation Website and Salesforce Stackexchange. The purpose of this post is to share the knowledge. I hope the developers who are new to SFMC or AMPScript will find this helpful!