AMPScript Lookup Examples

In Marketing Cloud, there are several ways to retrieve external data with AMPScript inside your email, landing page or SMS message. Here are few ways to go about it.

Suppose you have a Data Extension that contains these rows (and is not your sending audience):

LookupColumnDEColumn1DEColumn2
wheeNeoKeanu Reeves
wheeTrinityCarrie-Anne Moss
wheeMorpheusLaurence Fishburne
whoaAgent SmithHugo Weaving

Lookup value of single column value from a certain row

Output

DEColumn1 is Agent Smith

Lookup multiple column values from a single row

Output

DEColumn1 is Neo, DEColumn2 is Keanu Reeves

Lookup multiple column values from multiple ordered rows

Output

Row 1, DEColumn1 is Trinity, DEColumn2 is Carrie-Anne Moss
Row 2, DEColumn1 is Neo, DEColumn2 is Keanu Reeves
Row 3, DEColumn1 is Morpheus, DEColumn2 is Laurence Fishburne

For further reference:

Updates

  • 2017-10-19 – Added example Data Extension and output



24
Leave a Reply

avatar
12 Comment threads
12 Thread replies
4 Followers
 
Most reacted comment
Hottest comment thread
11 Comment authors
JasGwenLynneEbonyCarlito Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Javier
Guest
Javier

Hello, I cant access the data extension data with a lookup function if it is a shared data extenion

Sebastien
Guest
Sebastien

Hey Adam,
I’ve been trying to retrieve a row filtering on a datetime field.
I’ve tried so many different datetime formats and am still not getting any results.
To be precise what I am trying to do is update a row in SSJS but it is the same issue in AMPScript.
Have you ever managed to retrieve/update a row using a datetime value?
Thanks for your help!
Sebastien

trackback

[…] AMPScript Lookup Examples […]

chethan
Guest
chethan

is there a way to find lookup unique records?

Ryan
Guest
Ryan

Hey Adam,

I love that you have the code laid out, but it is hard to see how that would connect to the data extension, without having the data extension info listed. Could you possibly give an example with this code using example data extension info. I am working in Marketing cloud. I don’t know if that makes a difference for you.

Thanks for any help!

Ryan
Guest

Thanks for adding the example output!

Any chance you can add an example where the @numRowsToReturn isn’t 0? I’m attempting to complete this with a set number (3), but its only returning a single row.

Carlito
Guest
Carlito

Awesome website. Thanks Adam for sharing these!

Ebony
Guest
Ebony

What I want to do is dynamically populate my email based on the Value of every row under a column. So if one of the rows in the column = 1 I want to display a “1 icon” and if another Row = 2 I want to display a “2 icon” and so and so on. I think this code is on the right track of what I’m trying to do but the set @lookupValue = “whoa” as I’m not trying to match it with a specific value in another column

Lynne
Guest
Lynne

Sorry for the newbie question but, what if you have different data extensions in different folders? How do you access them?

Gwen
Guest
Gwen

Hi Adam, I’ve looked all over and can’t find how to do a lookuporderedrows with a field equal to either of two different values. I tried this:
set @sdRS = LookupOrderedRows(‘Account_Salesforce’, @numRowsToReturn, ‘Name Asc’, FIELD(‘RecordTypeId’, ‘012410000012dFjAAI, 012410000012ftqAAA’)) and it doesn’t work. Is this possible?

Jas
Guest
Jas

Hi I am a newbee and very new to SFMC too. I need some help. Below is the what I want to do. I have 3 data extension. 1 – Female List 2 – Male List 3 – Blank List Source Data Extension has 1000 records with 10 fields, Gender is one of the fields. Now if the Gender in the source field is Female I want the data(all 10 fields) to flow to 1 – Female List DE. If gender is Male then 2- Male List. If the gender field is blank then 3 – Blank List DE. Can… Read more »