title: Generate All Permutations in Excel using LAMBDA
body: <p>This is a frequently asked and answered question: <strong>how can I generate all permutations in Excel</strong>:</p>
  <p><a href="https://stackoverflow.com/questions/5724373/permutations-in-excel">2011</a>
  <a href="https://stackoverflow.com/questions/41053936/generate-all-permutation-of-multiple-lists-in-excel">2016</a>
  <a href="https://stackoverflow.com/questions/46101847/excel-permutation-table-without-vba">2017</a>
  <a href="https://superuser.com/questions/1194312/excel-create-table-with-all-combinations/1194380#1194380">2017 superuser</a>
  <a href="https://stackoverflow.com/questions/48651400/how-to-list-all-possible-combinations-of-the-values-in-three-columns-in-excel">2018</a>
  <a href="https://stackoverflow.com/questions/67905393/how-to-generate-all-combinations-using-5-columns-using-a-formula-in-excel/67926177#67926177">2021</a></p>
  <p>and now in <a href="https://stackoverflow.com/questions/71175705/excel-loop-from-list-with-formulas?noredirect=1#comment125834346_71175705">2022</a> which did not get an answer before it was closed as a duplicate, which is unfortunate because <strong>LAMBDA</strong> really changes the way that this question would be answered.</p>
  <p>I have infrequently had the same need and get frustrated by having to reinvent a complex wheel. So, I will re-pose the question and put my own answer below. I will not mark any submissions as the answer, but invite good ideas. I am sure that my own approach can be improved upon.</p>
  <h2>Restating the 2022 question</h2>
  <p>I am trying to create a loop in excel with formulas only. What I am trying to achieve is described below. Let's say I have 3 columns as inputs: (i) Country; (ii) variable; and (iii) year. I want to expand from these inputs to then assign values to these parameters.</p>
  <p><strong>Inputs:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Country</th>
  <th style="text-align: left;">Variable</th>
  <th style="text-align: right;">Year</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">GB</td>
  <td style="text-align: left;">GDP</td>
  <td style="text-align: right;">2015</td>
  </tr>
  <tr>
  <td style="text-align: left;">DE</td>
  <td style="text-align: left;">area</td>
  <td style="text-align: right;">2016</td>
  </tr>
  <tr>
  <td style="text-align: left;">CH</td>
  <td style="text-align: left;">area</td>
  <td style="text-align: right;">2015</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Outputs:</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Country</th>
  <th style="text-align: left;">Variable</th>
  <th style="text-align: right;">Year</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">GB</td>
  <td style="text-align: left;">GDP</td>
  <td style="text-align: right;">2015</td>
  </tr>
  <tr>
  <td style="text-align: left;">GB</td>
  <td style="text-align: left;">GDP</td>
  <td style="text-align: right;">2016</td>
  </tr>
  <tr>
  <td style="text-align: left;">GB</td>
  <td style="text-align: left;">area</td>
  <td style="text-align: right;">2015</td>
  </tr>
  <tr>
  <td style="text-align: left;">GB</td>
  <td style="text-align: left;">area</td>
  <td style="text-align: right;">2016</td>
  </tr>
  <tr>
  <td style="text-align: left;">DE</td>
  <td style="text-align: left;">GDP</td>
  <td style="text-align: right;">2015</td>
  </tr>
  <tr>
  <td style="text-align: left;">DE</td>
  <td style="text-align: left;">GDP</td>
  <td style="text-align: right;">2016</td>
  </tr>
  <tr>
  <td style="text-align: left;">DE</td>
  <td style="text-align: left;">area</td>
  <td style="text-align: right;">2015</td>
  </tr>
  <tr>
  <td style="text-align: left;">DE</td>
  <td style="text-align: left;">area</td>
  <td style="text-align: right;">2016</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>How can I do that efficiently using Excel?</p>
  <h2>Expanding the 2018 question</h2>
  <p>I have three columns, each of which has different kinds of master data as shown below:</p>
  <p><a href="https://i.stack.imgur.com/19ZZ7.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/19ZZ7.jpg" alt="enter image description here" /></a></p>
  <p>Now, I want to have all possible combinations of these three cells - like</p>
  <pre><code>aa kk jj
  aa kk ff
  aa ll jj
  aa ll ff
  aa mm jj
  ...
  </code></pre>
  <p>Can this be done with a formula. I found one formula with 2 columns, but i'm not able to extend it to 3 columns correctly</p>
  <p>Formula with 2 columns:</p>
  <pre><code>=IF(ROW()-ROW($G$1)+1&gt;COUNTA($A$2:$A$15)*COUNTA($B$2:$B$4),&quot;&quot;,
  INDEX($A$2:$A$15,INT((ROW()-ROW($G$1))/COUNTA($B$2:$B$4)+1))&amp;
  INDEX($B$2:$B$4,MOD(ROW()-ROW($G$1),COUNTA($B$2:$B$4))+1))
  </code></pre>
  <p>where G1 is the cell to place the resulting value</p>
  <h2>Common Requirements</h2>
  <p>What these have in common is that they are both trying to create an ordered set of permutations from an ordered set of symbols. They both happen to need 3 levels of symbols, but the 2018 question was asking for help to go from 2 levels to 3 and the 2021 question was asking to go from 3 levels to 5. The 2022 question is just asking for 3 levels, but the output needs to be a table.</p>
  <p>What if we go up to 6 levels like this?</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">L1</th>
  <th style="text-align: left;">L2</th>
  <th style="text-align: left;">L3</th>
  <th style="text-align: left;">L4</th>
  <th style="text-align: left;">L5</th>
  <th style="text-align: right;">L6</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">U</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">B</td>
  <td style="text-align: left;">G</td>
  <td style="text-align: left;">L</td>
  <td style="text-align: left;">Q</td>
  <td style="text-align: left;">V</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">C</td>
  <td style="text-align: left;">H</td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;">R</td>
  <td style="text-align: left;">W</td>
  <td style="text-align: right;">3</td>
  </tr>
  <tr>
  <td style="text-align: left;">D</td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;">X</td>
  <td style="text-align: right;">4</td>
  </tr>
  <tr>
  <td style="text-align: left;">E</td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: left;"></td>
  <td style="text-align: right;"></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This would generate 1'440 permutations.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">L1</th>
  <th style="text-align: left;">L2</th>
  <th style="text-align: left;">L3</th>
  <th style="text-align: left;">L4</th>
  <th style="text-align: left;">L5</th>
  <th style="text-align: right;">L6</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">U</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">U</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">U</td>
  <td style="text-align: right;">3</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">U</td>
  <td style="text-align: right;">4</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">V</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">V</td>
  <td style="text-align: right;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">V</td>
  <td style="text-align: right;">3</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">V</td>
  <td style="text-align: right;">4</td>
  </tr>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">F</td>
  <td style="text-align: left;">K</td>
  <td style="text-align: left;">P</td>
  <td style="text-align: left;">W</td>
  <td style="text-align: right;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">...</td>
  <td style="text-align: left;">...</td>
  <td style="text-align: left;">...</td>
  <td style="text-align: left;">...</td>
  <td style="text-align: left;">...</td>
  <td style="text-align: right;">...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Making a general formula that takes in any number of levels (columns) is hard.</strong> Just go through the answers provided - they each required some rocket science and until now, all the solutions had hard-coded limits on the number of columns of symbols. So <strong>can LAMBDA give us a general solution?</strong></p>