Lookup fonction ?

[Guest]

I need to find a formula which when I enter a value between 1 and 4 will copy the four names (from the data below) into the active cell and the three other names in the next three cells to the right of the active cell. For example: If cell a4=2, then in cells a5, b5, c5 and d5 the names W.Linsenmeyer; S.Plescia; B.Skeates; and P.Doyle respectively would be entered automatically. How should I go about this?


1 2 3 4
M.Laughlin W.Linsenmeyer K.Delameter D.Gray
R.Martin S.Plescia G.Dery G.Quintal
S.Chambers B.Skeates C.Mowat G.Tyson
D.Mackay P.Doyle T.Postans B.Fullerton

 

[Dave Smith]

A B C D
4 2
5 Linsenmeyer Plescia Skeates Doyle
6
7
8
9 Laughlin Linsenmeyer Delameter Gray
10 Martin Plescia Dery Quintal
11 Chambers Skeates Mowatt Tyson
12 Mackay Doyle Postans Fullerton

Put this formula in A5 and copy across:

=INDEX($A$9:$D$12,COLUMN(),$A$4)

HTH

I need to find a formula which when I enter a value between 1 and 4 will

copy the four names (from the data below) into the active cell and the three
other names in the next three cells to the right of the active cell. For
example: If cell a4=2, then in cells a5, b5, c5 and d5 the names
W.Linsenmeyer; S.Plescia; B.Skeates; and P.Doyle respectively would be
entered automatically. How should I go about this?

 

[Harlan Grove]

...
...

Put this formula in A5 and copy across:

=INDEX($A$9:$D$12,COLUMN(),$A$4)

...

Depends on the leftmost cell in the result range being in column A, so could be
broken if moved elsewhere or columns inserted. A more robust alternative is

=TRANSPOSE(INDEX($A$9:$D$12,0,$A$4))

but it needs to be entered into A55 (or wherever the 4 column by 1 row result
range is) all at once as an array formula.

 

[Dave Smith]

...
..
..

Depends on the leftmost cell in the result range being in column A, so could be
broken if moved elsewhere or columns inserted. A more robust alternative is

=TRANSPOSE(INDEX($A$9:$D$12,0,$A$4))

but it needs to be entered into A55 (or wherever the 4 column by 1 row result
range is) all at once as an array formula.

Interestingly, I had thought to suggest copy, paste special -> transpose but
I didn't make the next step to the transpose function.

Thanks.

 

[Guest]

Thanks Dave and Harlan. While I was waiting for your suggestions, I finally understood the LOOKUP function and used it and it works perfectly. Thanks again for your help.


----- Dave Smith wrote: -----

...
.. could be
broken if moved elsewhere or columns inserted. A more robust alternative is result
range is) all at once as an array formula.

Interestingly, I had thought to suggest copy, paste special -> transpose but
I didn't make the next step to the transpose function.

Thanks.