title: Index/Match Visible Cells Only
body: <p>I have an Excel workbook with two worksheets. The first holds a list of projects as follows:</p>
  
  <pre><code>Project ID    Project Name
  1             Project 1
  2             Project 2
  3             Project 3
  </code></pre>
  
  <p>The second holds comments related to the projects:</p>
  
  <pre><code>Project ID    Comment
  1             First Comment
  1             Second Comment
  2             Third Comment
  3             Fourth Comment
  3             Five Comment
  </code></pre>
  
  <p>My goal is to filter the comments list to only show the comments related to projects that are displayed, so if I filter out Projects 2 and 3, the comments list only shows as follows:</p>
  
  <pre><code>Project ID    Comment
  1             First Comment
  1             Second Comment
  </code></pre>
  
  <p>I am able to filter comments currently by determining if their ID matches an ID in the field, and if so, I have a column filter applied to only show matches. This is done in case someone deleted a project but did not delete the comments related to a project.</p>
  
  <pre><code> =IF(ISERROR(MATCH([@[Project ID]],ProjectWorksheet[Project ID], 0)), "No Match", "Match")
  </code></pre>
  
  <p>The problem I have is that if I filter out projects, it shows all comments because Excel is matching against all projects even if they are hidden by the filter instead of only showing comments that match "displayed" projects.</p>
  
  <p>I only want comments shown by the displayed projects.</p>
  
  <p>I have a macro in another workbook that Joins fields based on if the row of data is hidden or not, but is this methodology something I can use so that I can only see comments of projects that are displayed (visible). Here is the macro:</p>
  
  <pre><code>Function JoinAll(ByVal BaseValue, ByRef rng As Range, ByVal delim As String)
  Application.Volatile
  For Each a In rng
  If a = BaseValue And a.EntireRow.Hidden = False Then
  JoinAll = JoinAll &amp; IIf(JoinAll = "", "", delim) &amp; a(1, 7)
  End If
  Next a
  End Function
  </code></pre>
  
  <p>If possible, I would love to use a formula.</p>