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 email@example.com TX firstname.lastname@example.org TX
Data e xtension 2 data example: Email Group email@example.com A firstname.lastname@example.org B email@example.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 "firstname.lastname@example.org"
The search result should be:
email@example.com A firstname.lastname@example.org 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 = "email@example.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 ]%%