AMPScript to fetch values from data extension

Question

I have a Data extension and an HTML paste Email. In one of the email content block I would like to fetch some data dynamically using AMPScript from DE based on data attributes.

I have tried using lookuprows function but was not successful.

For example, there is an email address, which can possibly be present multiple times in the DE for separate products. So for each product, email should populate different fields in different emails to same subscriber.

I would like to populate below mentioned content block for each subscriber with "productURL" "productImage" "ProductName" from the DE to this content block, each identified based on the productname.

<td align="center" width="180">
<a conversion="true" href="href="%%[
SET @rowset = LookupRows("DataextensionTest","ProductNumber", testproduct, "ProductNumber", celebrate300000)
SET @row = Row(@rowset, 1)
]%%"
 " target="_blank">
       <img src="SSSSS" alt="Recommended" width="180" height="180" border="0" style="border:none; margin: 0; padding:0;">
      </a>
    </td

>

Any help with the AMPScript code to use in the content block would be highly appreciated.

Also would like to know if I have over 100 products would it be good to use if else condition to get the desired result. If so please provide the help.

error: ×The subscriber preview failed to generate. Review the details, correct all issues, and try again.
There is an error in your email. Please contact your customer service representative.
 Error 1: Script SET Statement Invalid
 An error occurred when attempting to resolve a script expression. See inner exception for detail.
 Script Expression: LookupRows("DataextensionTest","ProductNumber", bairon2990, "ProductNumber", celebrate300000)
 MemberID: 00
 JobID: 0

 The specified attribute or custom object field name was not found for this client.
 Function Call: LookupRows("DataextensionTest","ProductNumber", bairon2990, "ProductNumber", celebrate300000)
 Attribute or Field Name: bairon2990

 Invalid Content: 
 SET @rowset = LookupRows("DataextensionTest","ProductNumber", bairon2990, "ProductNumber", celebrate300000)

Answer 1

Currently your lookuprows() function is contradicting itself. it's built for one pair of operators to filter the data, and additional pairs are set to use the AND boolean operator. Meaning you're looking in "Dataextensiontest" for rows that have ProductNumber equal to testproduct AND ProductNumber equal to celebrate300000 which by default would return no data, since one value can't equal two things.

In order to write the lookuprows we'd need to know what columns are included in your "DataextensionTest" DE

I would also separate your Ampscript and HTML to help keep things clear, and also currently you're not defining any variables to actually use the data in the @row variable. you could try something like this:

%%[
SET @rowset = LookupRows("DataextensionTest","ProductNumber", testproduct)
SET @row = Row(@rowset, 1)
SET @url = Field(@row, "productURL")
SET @image = FIELD(@row, "productImage")
SET @name = FIELD(@row, "productName")
]%%
<td align="center" width="180">
  <a conversion="true" href="%%=v(@url)=%%" target="_blank">
    <h1>%%=v(@name)=%%</h1>
    <img src="%%=v(@image)=%%" alt="Recommended" width="180" height="180" border="0" style="border:none; margin: 0; padding:0;">
  </a>
</td>

This is assuming your image column in the data extension just houses a URL to the image.