Google Sheets – How to Add Multiple Columns in Google Sheets

Ok, I think I've figured it out. Not straightforward, but now that it's done, not terribly difficult.

**=VLOOKUP(D1,'TARGET_SHEET'|A:B,2)**

Some explanations about why I couldn't get the answer.

• Criterion (the D1 here) does not need to be a string. It can be a cell reference.
• A1 notation for cells can be used to refer to complete columns (did not know that). So A:B is perfect syntax in A1 notation. http://msdn.microsoft.com/en-us/library/office/bb211395%28v=office.12%29.aspx
• The array by definition needs to be 2 or more columns. (duh!) The value for the index parameter will likely be "2" (for a two column array). If the array had 3 columns, more than likely the value would be 2 or 3.
• You can auto-increment the values for this vlookup formula by grabbing the handlebars and pushing them down on the result column. So: in the cell immediately below the one with the formula, you will see =VLOOKUP(D2,'TARGET_SHEET'|A:B,2)

I offer two versions, depending on whether blank cells should be kept. The basic idea is the same:

1. Pick a character that is not contained in your table (e.g., an exotic Unicode character accessible via CHAR).
2. Use it in the JOIN command for each of three columns.
3. Concatenate the results, adding the character in between.
4. SPLIT the concatenated string by the same character.
5. TRANSPOSE the result.

For example:

=TRANSPOSE( SPLIT( JOIN(CHAR(57344),A1:A) &CHAR(57344)& JOIN(CHAR(57344),B1:B) &CHAR(57344)& JOIN(CHAR(57344),C1:C), CHAR(57344) ) )

where for joining/splitting, I picked the character CHAR(57344), which is designated for private use and therefore should not be present in any valid input.

The above formula removes blank entries, because this is what SPLIT does. This may be convenient if your data does not have blanks: saves you the trouble of tracking where the last row of the data is. But other times, blanks should be preserved.

To preserve blanks, I follow the answer by Jacob Jan Tuinstra except that I used another private use Unicode instead of space (who knows, maybe you have some cells that contain just a space and you want to keep those). This involves two extra steps: after joining everything as before, I use SUBSTITUTE to replace CHAR(57344) by CHAR(57344)&CHAR(57345), then split as before, and replace CHAR(57345) by empty string.

=TRANSPOSE( ARRAYFORMULA(SUBSTITUTE( SPLIT( SUBSTITUTE(...joins... , CHAR(57344),CHAR(57344)&CHAR(57345)), CHAR(57344) ), CHAR(57345), "")))

The full result looks like this (notice that now I specify where the last row of data is):

=TRANSPOSE( ARRAYFORMULA(SUBSTITUTE( SPLIT( SUBSTITUTE(JOIN(CHAR(57344),A1:A9) &CHAR(57344)& JOIN(CHAR(57344),B1:B9) &CHAR(57344)& JOIN(CHAR(57344),C1:C9), CHAR(57344),CHAR(57344)&CHAR(57345)), CHAR(57344) ), CHAR(57345), "" )))

One more caveat: strings in Google Sheets cannot exceed 50000 characters in length. If your data is too large for string-based approach to work, use a script (this one is a good place to start).