The LookupRows function returns the rows of a rowset in a data extension.

The LookupRows function is arguably the most useful AMPscript function available.

Use case: the LookupRows function can be used retrieve data values from a data extension as a rowset and set them as variables. These variables then can be used as logic and/or used to display dynamic content in email or landing pages.

For example, I want to retrieve values from a data extension and use the If Function to run if/then statements based on the values in specific fields — i.e. I have customer demographics such as gender, age, and city I want to display different content based on male or female values and show their age, but only for people who live in a specific city.

Syntax:

LookupRows(1,2,3)
1 = Data Extension name to perform the lookup
2 = Column or field name in the data extension use to match values to perform lookup
3 = Value used to match to return rows

Example:

 
%%[
set @lookupValue = “San Francisco”
set @rows = LookupRows(“YourDataExtensionName”,”City”, @lookupValue)
set @rowCount = rowcount(@rows)
if @rowCount > 0 then
    set @row = row(@rows,1)
    set @Gender = field(@row,”Gender”)
    set @Age = field(@row,”Age”) 
    if @Gender is “Male” then 
         set @msg = “This is male content.”
    elseif @Gender == “Female” then 
         set @msg = “This is female content.”
    endif
    ]%% Thank you for visiting AMPscript.com. %%=v(@msg)=%%. Your age is: %%=v(@Age)=%% %%[
else
    ]%% No rows found %%[
endif
]%%

Output:

Thank you for visiting AMPscript.com. This is male content. Your age is: 69

Explanation:

There are a lot of things going on in this example. First we set the @lookupValue to a hardcoded value of “San Francisco.” This means that we only want to retrieve rows for customers that have a city of “San Francisco.”

In the LookupRows example above, the data extension name is “YourDataExtensionName” (I know, it’s not very clever). In the example, the data extension has a column called “City” where we’re finding all records that have a value of “San Francisco.”

You’ll notice that we’re setting the LookupRows function as a variable to @rows with the reasoning being that we want to perform a Rowcount first to see if any rows are returned. If the Rowcount is greater than 0, meaning 1 or more row has a value of “San Francisco” then we continue with the AMPscript code. If zero rows are returned, in other words, we have no customers with a value of “San Francisco” as the City, then we the If Function defaults to the else portion and returns the string of “No rows found.”

Next we use the Row function to return the rows from the rowset. Another way to think about this is that the LookupRows function returns a group of rows, or a set of rows — in other words a rowset. We want to return the specified rows and assign variables to them.

The variables @Gender and @Age are set using the Field function which returns the field within the row. Think of it like:

Rowset > Row > Field

Lastly, we use a nested If Function to put in logic to determine male or female values within the gender column. Then we output the message based on those values.

Related functions: If Function, RowCount function, Row function, Field function, Lookup Function