title: if match with multiple columns in Power Query
body: <p>I have a two tables, In Table 2 columns from B to F need to be matched from Table 1 columns from A to G. If matched then the status is &quot;YES&quot; if not matched then the status is &quot;NO&quot;.</p>
  <p>I am achieving the my output the following formula in Excel</p>
  <pre><code>G2=IF(AND(SUMPRODUCT((TABLE1!A$2:A$18=B2)*(TABLE1!B$2:B$18=C2))&gt;0,IF(B2=&quot;Banana&quot;,D2=1,D2&gt;1),SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!D$2:D$18=E2))&gt;0,SUMPRODUCT((TABLE1!B$2:B$18=C2)*(TABLE1!E$2:G$18=F2))&gt;0),&quot;YES&quot;,&quot;NO&quot;)
  </code></pre>
  <p>but How can I apply the same in thing Power Query.</p>
  <p><a href="https://i.stack.imgur.com/qSej9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qSej9.png" alt="enter image description here" /></a></p>
  <ul>
  <li><p>Example for line item 2; (In Table 2 Data) The item is 123 the fruit list is &quot;Avocado&quot; with area code is &quot;3100&quot; it's matched to my master data Table 1 therefore the status is &quot;YES&quot;.</p>
  </li>
  <li><p>Example for line item 5; (In Table 2 Data) The item is 126 the fruit list is &quot;Avocado&quot; with area code is &quot;West&quot; it's not matched to my master data Table 1 therefore the status is &quot;NO&quot;.</p>
  </li>
  </ul>
  <p>The &quot;Avocado&quot; &quot;area code&quot; belongs to only the following sales code (EU01,US,UK).</p>
  <ul>
  <li>Example for line item 19; The item is 56 the fruit list is &quot;watermelon&quot; doesn't have a area code. Please refer the above mentioned snapshot.</li>
  </ul>
  <p>Match criteria: The matching selection criteria is &quot;Fruit list&quot; and &quot;area code&quot; in both tables.</p>
  <p>Qty columns conditions; The qty columns shouldn't be blanks or 0. it's always greater than 1 expect the Banana with area code &quot;PP1 and &quot;KP1&quot; its always equal to 1.</p>
  <p>Sales code conditions; The sales code columns shouldn't be blanks or 0.</p>
  <p>File attached for your reference.
  <a href="https://www.dropbox.com/scl/fi/8b4cnamqzsk2rannvowkk/POER-BI-DATA-SOF.xlsx?dl=0&amp;rlkey=zt7y1rg2di1b53r6ta2kb3jj0" rel="nofollow noreferrer">https://www.dropbox.com/scl/fi/8b4cnamqzsk2rannvowkk/POER-BI-DATA-SOF.xlsx?dl=0&amp;rlkey=zt7y1rg2di1b53r6ta2kb3jj0</a></p>
  <p><a href="https://i.stack.imgur.com/14fxT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/14fxT.png" alt="enter image description here" /></a></p>
  <p><a href="https://i.stack.imgur.com/qTDvh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qTDvh.png" alt="enter image description here" /></a></p>