title: How to move data to right empty cells in Google Sheets?
body: <p>I would like to design an organization chart and I am building a supervisor tree like this. </p>
  
  <pre><code>9  8  7  6  5  4  3  2  1  0
  A  B  C                    
  D
  E  F  B  C
  </code></pre>
  
  <p>By using vlookup I got the table like above. </p>
  
  <p>Now I would like the table to be like this:</p>
  
  <pre><code>9  8  7  6  5  4  3  2  1  0
                       A  B  C
                             D
                    E  F  B  C
  </code></pre>
  
  <p>I tried </p>
  
  <pre><code>=IFS(ISBLANK(I2),H2,ISBLANK(H2),G2,ISBLANK(G2),F2,ISBLANK(F2),E2,ISBLANK(E2),D2,ISBLANK(D2),C2,ISBLANK(C2),B2,ISBLANK(B2),A2)
  </code></pre>
  
  <p>But it turned out to be <code>#REF</code></p>
  
  <p>I just would like to move the data to the right, to get rid of those empty cells. </p>
  
  <p>Does anyone know how to do it? Or can we realize it in BigQuery?</p>