Help with formula involving relative link/reference

[Hull]

Hello!

I'm trying to build a formula, and having difficulty with building a relative link/reference.

The workbook in which I want to place the formula has 2 worksheets.

Sheet1 is a source sheet which contains all of the data
Sheet2 is the destination sheet in which data from the source sheet will be automatically populated by the formula

In both sheet one and sheet two, the first column is the id.

So, in the destination sheet, when I enter an id from the source sheet, I want all of the columns to the right of the ID column to automatically populate with the data from the row of the ID in the source sheet. For example:

If the source sheet contains this data:

1 Apples Apples Apples Apples Apples
2 Oranges Oranges Oranges Oranges Oranges
3 Pears Pears Pears Pears Pears
4 Bananas Bananas Bananas Bananas Bananas

And I enter "3" into the first (ID) column on the destination sheet, the five columns to the right in that row would automatically populate with the word "Pears." They would populate with "Apples" if I entered "1" in the first, ID column.

Hope this makes sense, please let me know if you have any suggestions or questions.

Thanks!

Hull

[carsto]

assuming your data is in columns A thru E and rows 1 thru 4 [A1:E4] on Sheet 1
and the first (ID) column on Sheet 2 is column A,

place this formula into B2
=INDEX(Sheet1!$A$1:$E$4,MATCH($A2,Sheet1!$A$1:$A$4,0),2)

you can copy this across, but you will need to change the column number for each column to the right.
the ,2) at then end of the formula becomes 3, 4, 5 respectively

(I know of no way to make this a relative ref)


then you can copy down

[Hull]

Fantastic! That did the trick. Thanks a lot, Carsto!

[carsto]

I said "I know no way to make the column number a relative reference", but I learned a trick from ChemistB on this forum just today.

Change the ,2) to ,Column(B$1)) and it will change as you drag across!

Column(B$1) = 2
Column(C$1) = 3

what an incredibly useful idea!