title: How to convert a data matrix into a relational table using Google Sheets formula?
body: <p>I've got a set of data in a matrix form and I'm trying to use a formula to generate a relational table from that matrix. Here's an illustration of what I'm trying to achieve:</p>
  
  <p>Matrix:</p>
  
  <pre><code>        | 3/5 | 4/5 | 5/5 |
  ---------------------------
  Player1 |  1  |  0  |  1  |
  Player2 |  0  |  1  |  2  |
  Player3 |  1  |  1  |  2  |
  </code></pre>
  
  <p>Goal:</p>
  
  <pre><code>Player  | Date | Value |
  ------------------------
  Player1 | 3/5  |   1   |
  Player3 | 3/5  |   1   |
  Player2 | 4/5  |   1   |
  Player3 | 4/5  |   1   |
  Player1 | 5/5  |   1   |
  Player2 | 5/5  |   2   |
  Player3 | 5/5  |   2   |
  </code></pre>
  
  <p>See the real data here:
  <a href="https://docs.google.com/spreadsheets/d/1DNaoBnYMNbznUoAJahW9-UfGq6LbTG3TXgXPz90bWas" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1DNaoBnYMNbznUoAJahW9-UfGq6LbTG3TXgXPz90bWas</a></p>
  
  <p>Anyway, I followed the instructions in this <a href="https://stackoverflow.com/a/26539962/809112">answer</a> and came up with this formula:</p>
  
  <pre><code>=ArrayFormula(QUERY(VLOOKUP(HLOOKUP(matrix!A3,matrix!A3:A,INT((ROW(matrix!A3:A)-
   ROW(matrix!A3))/COLUMNS(matrix!B1:Z1))+1,0),{matrix!A3:T, IF(ROW(matrix!A3:A), 
   matrix!B1:Z1)}, {SIGN(ROW(matrix!A3:A)), MOD(ROW(matrix!A3:A) - 
   ROW(matrix!A3), COLUMNS(matrix!B1:Z1)) + {2, 2+COLUMNS(matrix!B1:Z1)} }, 0 ), 
   "select Col1, Col3, Col2 where Col2 is not null and Col2 != 0 order by 
   Col3,Col2", 0))`
  </code></pre>
  
  <p>It does work, sort of, but with the downside that to generate the rows it uses a lot of empty rows in the matrix sheet, and practically requires that the matrix sheet have at least numCols*numRows number of rows. Now I could just add 3500 empty rows to the matrix and be done with it, but somehow it feels like there should be a better way. Any ideas on how to improve it?</p>