I am using lookup to search records.

In Data extension 1, I have EmailAddress set as primary key In Data extension 2, I am allowing multiple rows of same email address

Data extension 1 data example: Email State 1@1.com TX 2@1.com TX

Data e xtension 2 data example: Email Group 1@1.com A 2@1.com B 1@1.com C

So, in the search results by lookup, i want to display ALL rows that meets the search criteria.

In this example, if I lookup search for email "1@1.com"

The search result should be:

1@1.com A 1@1.com C

This is the ampscript that I am using and currently the issue is that i am getting ONLY 1 row for the search term when there should be more than 1 row as the search term (email) exists more than once in dataextension2.

var @rows, @row, @rowCount, @numRowsToReturn, @lookupValue, @i, @j, @rowTWO, @lookupValueTWO, @row2ndDE 

set @lookupValue = "1@1.com"
set @numRowsToReturn = 0
set @rows = Lookuprows("Dataextension1","Email", @lookupValue)
set @rowsTWO = Lookuprows("Dataextension1","Email", @lookupValue)
set @rowCount = rowcount(@rows)

if @rowCount > 0 then

for @i = 1 to @rowCount do

var @DEColumn1, @DEColumn2, @DEColumn3
set @row = row(@rows,@i) 
set @row2ndDE = row(@rowsTWO,@i) 
set @DEColumn1 = field(@row,"Email")
set @DEColumn2 = field(@row,"State")
set @DEColumn3 = field(@row2ndDE,"Group")


Email is %%=v(@DEColumn1)=%%, State is %%=v(@DEColumn2)=%%, Group is %%=v(@DEColumn3)=%%<br/>


next @i ]%%

%%[ else ]%%

No rows found

%%[ endif ]%%
  • Shouldn't the @rowsTWO lookup be referencing a different data extension? – Adam Spriggs Feb 24 '17 at 21:29

You can't mix row sets like that. You'll need to include an additional LookupRows():

var @rows, @row, @rowCount, @i
var @rows2 @row2, @rowCount2, @j

set @email = AttributeValue("_subscriberkey")
set @rows = LookupRows("DataExtension1","Email", @email)

set @rowCount = rowcount(@rows)

if @rowCount > 0 then

    for @i = 1 to @rowCount do

        var @EmailAddress, @State, @Group
        set @row = row(@rows,@i)

        set @EmailAddress = field(@row,"Email")
        set @State = field(@row,"State")

        outputline(concat("<br>", @i , ". Email: ", @EmailAddress))
        outputline(concat("<br>", @i , ". State: ", @State))        

        set @rows2 = LookupRows("DataExtension2","Email", @email)
        set @rowCount2 = rowcount(@rows2)

        if @rowsCount2 > 0 then

          for @j = 1 to @rowCount2 do

            set @row2 = row(@rows2,@j) 
            set @Group = field(@row2,"Group")

            outputline(concat("<br>", @j , ". Group: ", @Group))

          next @j


    next @i 


    outputline(concat("<br>no rows found"))


  • Thank you for the updated ampscript - I am able to get the desired result :) Thank you so much! Just a note if anyone else is looking for a similar solution. In the above code - a comma (,) is missing in declaration (@rows2 @row2) should be (@rows2, @row2) and at the end, please add --> ]%% – kuml Mar 1 '17 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.