title: Two way lookup within a sortable/filterable Excel table
body: <p>I am looking to return the intersection of a given row and column in an Excel table, where the sort/filter of the table will be changed.</p>
  <p>I have found lots of resources on how to do a two way lookup in a static Excel 2D array/table that will never change order, but I am running into trouble trying to reference these intersections of a given row and column in a table that will be filtered and sorted by users. Filters and sorts are changing the cells' locations, breaking my references.</p>
  <p>Ideally, the solution should look something like this:</p>
  <pre><code>=INDEX(TABLE,MATCH(&quot;PRODUCT&quot;,A:A,0),MATCH(&quot;COMPANY&quot;,$1:$1,0))
  </code></pre>
  <p>or, something like this,</p>
  <pre><code>=TABLE[[#Data],[PRODUCT] TABLE[[#All],[COMPANY]]
  </code></pre>
  <p>My environment is a table where the left most column is a list of products, and the top most row are column headers listing company names. The cell values at the intersection are quantities of products per company.</p>
  <p>Thank you for any help, in advance!!</p>
  <hr />
  <p>EDIT: Here's a visual example,</p>
  <pre><code>PRODUCTS    COMPANY A   COMPANY B
  PRODUCT #1  1           2
  PRODUCT #2  3           1
  </code></pre>
  <p>Referencing &quot;COMPANY B&quot;, &quot;PRODUCT #1&quot; would return 2-- what's key is that the reference isn't broken by the table being filtered or sorted.</p>