title: Finding matches in a matrix of cells against a second worksheet
body: <p>I'm attempting to generate a matrix of people who are in common with each other.  In one worksheet, I have the matrix itself, and in the other, I have the raw data.  The output needed is to put an "x" in the cell if there's a match, or nothing if there isn't a match.</p>
  
  <p>Here is a sample of the matrix worksheet, where the names going down in column A are the same names going across in row 1.  Names are in "firstname middlename lastname" format (usually):</p>
  
  <pre><code>   A     | B     | C     | D     | E    
   ------------------------------------
  1|       | Name1 | Name2 | Name3 | Name4
  2| Name1 |       |       |       |
  3| Name2 |       |       |       |
  4| Name3 |       |       |       |
  5| Name4 |       |       |       |
  </code></pre>
  
  <p>Here is a sample of the raw data worksheet.  Column A is a sorted list of names, and column B is who they are on common with.  There are hundreds of names in the list:</p>
  
  <pre><code>   A     | B     
   ------------------------------------
  1| Name1 | Name33
  2| Name1 | Name5
  3| Name1 | Name21
  4| Name2 | Name1
  5| Name2 | Name92
  ...
  </code></pre>
  
  <p>The formula I need to write goes into the blank cells in the matrix worksheet.  Here is an example of the final output (though there are many more names in the actual matrix):</p>
  
  <pre><code>   A     | B     | C     | D     | E    
   ------------------------------------
  1|       | Name1 | Name2 | Name3 | Name4
  2| Name1 |       |       | x     | x
  3| Name2 | x     |       | x     |
  4| Name3 | x     | x     |       |
  5| Name4 |       |       | x     |
  </code></pre>
  
  <p>Here's how far I've gotten before hitting a brick wall.  I attempted to write a VLOOKUP formula to do this, but it wouldn't work if I just told it to look in the entire range of the raw data worksheet because it would pick up matches from the wrong people.  I only want it to match against the part of column B that had the name in Column A for that cell.</p>
  
  <p>So, the closest I could get was to put in the specific range from the raw data file for the name listed on that particular row in the matrix.  For instance, I manually looked up Name1 in column A of the raw data file and found it existed in rows 39-62.  So, for Name1, if one of the names going across row 1 of the matrix is found between rows 39-62 of column B in Raw_Data, mark an "x":</p>
  
  <pre><code>=IF(IFNA(VLOOKUP(B$1,Raw_Data!$B39:$B62,1,FALSE),"")="","","x")
  </code></pre>
  
  <p>This would work only for one row in my matrix sheet.  Then, when going to the next row, I'd have to manually look up the Raw_Data rows for that person and put it in the formula.  Granted, it saved a little time, but not nearly as much as if I could get this formula to work across the whole matrix without any changes.</p>