Lookup()

Returns data from a specific column in a data extension. To call this function, you supply the name of the column to search, the value to match, and the column to return.

If your search criteria return more than one result, the system returns the first matching value it finds. For this reason, it’s best to use this function to search for identifiers that are unique within the data extension.

AMPscript also includes several functions that you can use to retrieve several rows of data.

  • The LookupRows() function retrieves one or more rows based on the criteria you specify.
  • The LookupRowsCS() function is a case-sensitive version of the LookupRows() 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().
  • dataExt (string): Required. The name of the data extension that contains the data that you want to retrieve.
  • returnColumn (string): Required. The name of the column to return data from.
  • searchColumn1 (string): Required. The name of the column to search. This value is case-sensitive.
  • searchValue1 (string): Required. The value that identifies the rows to retrieve. This value is case-sensitive.

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

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

NameENNameJALargestCityENLargestCityJAPopulation2020AreaKM2
Hokkaido北海道Sapporo札幌市540000083000
Tohoku東北地方Sendai仙台市890000067000
Kanto関東地方Tokyo東京4330000032000
Chubu中部地方Nagoya名古屋市2140000067000
Kansai関西地方Osaka大阪市2250000033000
Chugoku中国地方Hiroshima広島市730000032000
Shikoku四国Matsuyama松山市380000019000
Kyushu九州Fukuoka福岡市1430000044000

This code searches for a row in which the value of the NameEN column is Kanto. It then returns the value in the NameJA column for that row.

The code example outputs the value of the specified column.