I have two columns in Google Sheets, and I would like the product of a cross-join:



For example, if these were tables in SQL, and I did a cross-join, I would get

Days   Students
------ --------
1/1/15 Jake
1/1/15 John
1/1/15 Sally
1/1/15 Grace
1/2/15 Jake
1/2/15 John
1/2/15 Sally
1/2/15 Grace
1/3/15 Jake
1/3/15 John
1/3/15 Sally
1/3/15 Grace

Is there a simple way to do this in Google Sheets? I need to use Google Sheets to track students switching between classrooms (In week 1 Jake is in classroom A, but in week 2 Jake is in Classroom B). The only way I can figure to do this is to expand a list of students per day as above. However I'm definitely open to other suggestions.

  • Stick-a-round Jeff, answers might take a while...Welcome on Web Applications !!
    – Jacob Jan
    Commented Feb 17, 2015 at 19:40

4 Answers 4


assuming you have the names in A2:A5 and the dates in B2:B5, try this formula:

=ArrayFormula({transpose(split(join(char(9), rept(B2:B5&char(9),rows(B2:B5))),char(9))), transpose(split(rept(join(char(9),A2:A5)&char(9),rows(B2:B5)),char(9)))})

Example sheet with formula in D2


This formula consists of two parts, forming a literal array {first part, second part}. It is basically two times the same formula, applied one to the dates and once to the names.

  1. repeat (rept command) the dates and the names with the number of days (rows(B2:B5)).
    Difference: the dates are repeated first and then joined, the names are first joined then repeated.
  2. Everything is sucked in one cell with the join command and a char9 (tab) is added
  3. The tab (char9) is then used to split the contents of that cell
  4. the outcome of the split is transposed, so a column is generated

Hope that helps ?

  • 1
    This would be an awesome answer if you could explain what's happening in your function call.
    – ale
    Commented Feb 17, 2015 at 21:06
  • I added an explanation in the sheet I shared. If you still have question, don't hesitate to ask.
    – JPV
    Commented Feb 17, 2015 at 22:31
  • Which is great, but what happens when that sheet is no longer available? We like our answers to be self-contained.
    – ale
    Commented Feb 17, 2015 at 22:33
  • 1
    Added it to my answer. I hope that helps ?
    – JPV
    Commented Feb 17, 2015 at 22:45
  • 1
    A good example of inscrutable spreadsheet code. <grin> This is a limitation of spreadsheets. Rule of thumb: If you have more than 4 sets of parentheses your code is unmaintainable by non-wizards. Stack overflow for the reader. Commented Apr 3, 2015 at 14:11

Here's a custom formula that does the same.


 * Create a cross join of two ranges
 * @param {range} students The range with student names
 * @param {range} days The range of dates
 * @param {range} header The range with the headers
 * @return A cross join of the two ranges
 * @customfunction
function crossJoin(students, days, header) {  
  var output = [];
  if(header) {output.push([header[0][1], header[0][0]])};
  for(var i = 1, iLen = days.length; i < iLen; i++) {
    for(var j = 1, jLen = students.length; j < jLen; j++) {
      output.push([days[i][0], students[j][0]]);      
  return output;  


enter image description here

enter image description here


If a header has been selected, then add it first thing. Afterwards for all dates, all names are added. When all items are processed, then return the result.


The date output in Google Spreadsheet via a script is always considered a date. No formatting needed afterwards.


I've created an example file for you: Join two columns in Google Sheets.
The script can be added from the main menu Tools > Script editor. Save the script and the custom formula can be used right-away.

  • Nice answer. The code is clear even to someone like me who does write google script yet. Commented Apr 3, 2015 at 14:13

Directly from Google Documentation:

=ArrayFormula(SPLIT(FLATTEN(A1:A3 & "|" & TRANSPOSE(B1:B2)), "|"))



I solved this by using Javascript LINQ (language integrated query).

=linq("Enumerable.from('Orders').take(100).join('People', '$.Region', '$.Region',(l, r)=> {return {...l,...r}})", State!$A$1)

It lets you specify Javascript with complex join conditions. You can also perform other SQL queries such as Grouping, Projecting, Sorting and Filtering your sheets as if they were database tables. Look at the links below.

Note that in the LINQ query language I replaced all spaces in column names with underscores to make them valid JS identifiers.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.