Lookup Formula Question

[Guest]

I'm pretty new to excel and I'm wondering if anyone can help me.

I want to create a formula so that if A1=1, then F2:F5 are entered into C2:C5, or if A1=2, then G2:G5 are entered into C2:C5, etc. for more values forA1.

Ex:

A B C D E F G
1
2 5 6
3 10 7
4 15 8
5 20 9

I tried
=OR(LOOKUP(1,$A$1,(F2:F5)),(LOOKUP(2,$A$1,(G2:G5))))
but it returns#N/A.

any help would be greatly appreciated.

 

[Frank Kabel]

Hi
one way: enter the following in C2
=OFFSET(E2,0,$A$1)
and copy this down to C5

 

[mickedog]

you could try the index function:
copied from excel help

INDEX(array,row_num,column_num)

Array is a range of cells or an array constant.

If array contains only one row or column, the corresponding row_num o
column_num argument is optional.

If array has more than one row and more than one column, and onl
row_num or column_num is used, INDEX returns an array of the entire ro
or column in array.

Row_num selects the row in array from which to return a value. I
row_num is omitted, column_num is required.

Column_num selects the column in array from which to return a value
If column_num is omitted, row_num is required.


i could send you an example if u nee

 

[Guest]

an example would be great, im trying to figure all this out still
thanks frank, im going to try your suggestion too