Excel – Copy every nth column on a specific row from one sheet to another (in Google Sheets)

google-drivegoogle-spreadsheetsmicrosoft excelworksheet-function

I want a function to select every 2nd column on a particular row and display it in another sheet.

I have two sheets: Sheet1 and Sheet2

In Sheet1 A2 I have a list of numbers that start from 1 to 1000.

In Sheet1 A3 I have links that I want to select/display in Sheet2.

I want to choose what link to display in Sheet2 A2 by writing a function in Sheet2 A1 so that when I write "1" I get the link on the same row as page 1.

See example: Image (GIF)

And I want to do this without using macros if possible.

Someone suggested a function like this:

=OFFSET(Members profiles!$A$2,(ROW(B2)-1)*0,2)

I played with the numbers a bit but if this could be changed to help with my problem it would be great.

Best Answer

Let's see if either of these styles will get what you're looking for. I am a big fan of the INDEX(MATCH()) combo to find a value, but return back to me an associated value to that found value like you're needing (find the page number, but send back the link).

I have Sheet1 set up like you did:
enter image description here

And then I have two styles set up on Sheet2. Columns A & B would be what I suspect you will eventually move to, and columns D & E are what your sample was set up like.

Style A:

=INDEX(Sheet1!$B$1:$B$5,MATCH($A2,Sheet1!$A$1:$A$5,0))

You could copy this formula down the column and it will reference the static ranges from Sheet1, but look up the value from column A for each different row you copy the formula to.

enter image description here

Style B:

=INDEX(Sheet1!B1:B5,MATCH(E1,Sheet1!A1:A5,0))

This style will simply grab the link for a single value that you enter in cell E1.

enter image description here

Reference info here - http://www.contextures.com/xlFunctions03.html

EDIT: From comments; and I hope I understand the follow-up question correctly, but you can use the result of one of the Sheet2 formulas to concatenate stuff to the URL result, like the following example of adding "/index.htm" to one of them.

enter image description here

Related Question