sharing entire columns

[Guest]

I need to have the following lookup table work within multiple sheets.

sheet 1

cell c5 ( has a pulldown with 9 different values)

cells c10:c20 (blank cells needing values relative to value put in c5)

Sheet 2

cells a1:I20 (Are columns with 9 headings and each heading equal to one of
the pulldown in sheet1!a1:I20)

I need to have sheet 1 (c5) lookup the values in the column that is referred
to by its value on sheet 2 and grab all of the information in the nentire
column and place it it a column on sheet 1.

Exp. ( not a complete sheet)

(sheet 1)

a b c d e f
1
2
3
4
5 blank
6
7
8
9

all cells a1 through f5 are filled with values EXCEPT for cell c5. Cell c5
is a dropdown with values that are thr same as headings of columns in sheet 2.

(sheet 2)

a b c d e f
1 4610 5550 3550 3000 8550 9550
2 12 13 95 31 3 5
3 100 200 50 25 20 75
4 1 5 4 7 9 10
5 3 35 76 29 90 250

if sheet1!c5 = 3550 then cells sheet1!f6:f9 should be populated with values
95,50,4,76 from sheet2!c2:c5.


Can anyone help with this?

Thanks!!

 

[Barb Reinhardt]

f6 =INDEX(Sheet2!$A$1:$F$5,2,MATCH(C$5,Sheet2!$A$1:$F$1,0))
f7 =INDEX(Sheet2!$A$1:$F$5,3,MATCH(C$5,Sheet2!$A$1:$F$1,0))
f8 =INDEX(Sheet2!$A$1:$F$5,4,MATCH(C$5,Sheet2!$A$1:$F$1,0))
f9 =INDEX(Sheet2!$A$1:$F$5,5,MATCH(C$5,Sheet2!$A$1:$F$1,0))

Should get what you want.

 

[Guest]

Thanks Barb, worked like a charm!

f6 =INDEX(Sheet2!$A$1:$F$5,2,MATCH(C$5,Sheet2!$A$1:$F$1,0))
f7 =INDEX(Sheet2!$A$1:$F$5,3,MATCH(C$5,Sheet2!$A$1:$F$1,0))
f8 =INDEX(Sheet2!$A$1:$F$5,4,MATCH(C$5,Sheet2!$A$1:$F$1,0))
f9 =INDEX(Sheet2!$A$1:$F$5,5,MATCH(C$5,Sheet2!$A$1:$F$1,0))

Should get what you want.