Google-sheets – Vlookup across multiple columns

google sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

This question is related to the same project in this question, but I'm now at a later step.

I have a Google Sheet with a variety of German translations of a large set of English words. Using a separate sheet in the same workbook, I've assembled all the German translations from all the different translators in column A. Now I want to take the cleaned up list of all the translations and do a vlookup to get the English origin term.

In other words: in one sheet, I’ve got English terms in one column with German translations in the other columns; in this new sheet I’ve got German translations in one column and I want to look up the relevant English term in a second column (to perform some other operations that will happen at a later step).

I’ve tried using vlookup, but I can’t seem to get it to work because I need to look up across a range that covers several columns. I think what’s going on is that this is messing up the index, but there could be more to it that I’m not seeing. Can anyone help with that?

Best Answer

I ended up doing this the not-so-smart way: instead of making VLOOKUP happen across multiple columns, I copied all of the English terms multiple times and stacked them vertically in one column, then put the German translations vertically next to them. This wasn't exactly elegant and wouldn't have worked on a much larger data set, but it let me run the VLOOKUP that I needed.

Related Solutions

Google-sheets – Insert column across multiple sheets

You could always create the column in one sheet, and copy-paste it to the other sheets, one sheet at the time. But I guess that's what you're trying to avoid.

It is possible to do this with a Google Apps Script. The following script adds a menu in a spreadsheet, which allows you to insert or remove a column on all sheets.

function addColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Insert column right of column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.insertColumnAfter(colIdx);
  }
}

function removeColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Remove column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.deleteColumn(colIdx);
  }
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Add column on all sheets", functionName: "addColumnOnAllSheets"}, 
    {name: 'Remove column on all sheets', functionName: 'removeColumnOnAllSheets'}
  ];
  sheet.addMenu("Columns", entries);
}

To install this script in your own spreadsheet, click Tools -> Script editor, and paste the code into the script editor. Save the script, and reload the spreadsheet in order to show the menu.

I have set up an example spreadsheet to demonstrate the script.

Google Sheets – Importing Columns of Data

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)

Related Topic