4

I have Google Sheets Data.

alpha    | 0.1
beta     | 0.2
gamma    | 0.3
delta    | 0.4
epsilon  | 0.5

and another sheet in the same file, Data Subset, that holds an arbitrary number of row indices to the first sheet. E.g.:

2
4
5

What formula could I put in Data Subset that would allow me to pull data from the rows indicated by the first column?

e.g.:

2 | beta     | 0.2
4 | delta    | 0.4
5 | epsilon  | 0.5
  • 1
    I wasn't successful in creating a formula that uses ARRAYFORMULA or VLOOKUP. I hope that particular solution will come eventually. – Jacob Jan Tuinstra Dec 26 '13 at 21:12
  • @JacobJanTuinstra I hope so too. Your formula examples work, but only when exactly 3 rows index exactly 2 columns. I will post a scalable solution if I find one. – Drew Dormann Dec 28 '13 at 0:08
5

I found three solutions for you.

Formula

=INDIRECT("DATA!A"&A1)
enter image description here

Google Apps Script

function dataSubset(data,subset) {
  var output = [];
  for(var i=0, iLen=subset.length; i<iLen; i++) {
    for(var j=1, jLen=data.length+1; j<jLen; j++) {
      if(parseInt(subset[i]) == parseInt(j)) {
        output.push(data[j-1]);
      }
    }
  }
  return output;
}

enter image description here

Formula only available in the new Google Spreadsheet

=ARRAY_ROW(INDIRECT("DATA!A"&A1),INDIRECT("DATA!B"&A1))

Example

I've prepared two example files for you:

  1. Old
  2. New
1

You can simply use the index function.

In B1 enter:

=INDEX(Data!A$1:A$5,$A1)

then you can copy and paste this to the rest of the cells in columns B and C

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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