Thanks Tom for making it so clear, it all works now.

Ian.
-----Original Message-----
Select your three cells

go to the formula bar and enter
=VLOOKUP(1,A1:F10,{2,5,6})

Now, normally you would hit Enter to finish the editing,
but instead you
should hold down the ctrl and Shift keys, then while
holding them down, hit
the enter key as well. Ctrl+Shift+Enter

no select one of the cells (the formula should be
identical in all cells
rather than shifted as you show).


{=VLOOKUP(1,A1:F10,{2,5,6})}

the formula will appear to be in brackets as shown
above. these are not
actually present, but they are used by excel to give
feedback that the
formula has been entered as and is being treated as an
array formula.

If you now try to edit any single of those 3 cells, you
will get an error
that you can not edit that cell. You would need to again
select all 3 cells
and do the edit, then again close out with
Ctrl+Shift+Enter.

Now if you want to copy down as an example, you would
select all three
cells, then drag fill down.

If this is not clear to you, then reread the thread and
perhaps you will
understand one of the other explanations that have given
similar advice.

--
Regards,
Tom Ogilvy


wrote in message
...
Alan,

thanks for the reply but I'm still struggling. This is
the
data I'm trying this on:

a b c d e f
0.457 3.55 500 200 200 200
0.525 3.25 400 195 195 195
0.616 2.93 300 190 190 190
0.675 2.75 250 185 185 185
0.746 2.57 200 180 180 180
0.835 2.38 150 175 175 175
0.946 2.17 100 170 170 170
1.09 1.95 50 165 165 165
1.29 1.71 0 160 160 160


and I'm using this VLOOKUP function:
=VLOOKUP(1,A1:F10,{2,5,6})

but the result I get is:
2.17 #N/A #N/A

when I would expect/hope for:
2.17 170 170

If I look at the VLOOKUP formulae in the three
continuous
cell (horizontally) they read:
=VLOOKUP(1,A1:F10,{2,5,6}) then =VLOOKUP(1,B1:G10,
{2,5,6})
and =VLOOKUP(1,C1:H10,{2,5,6}).

I'm putting these formulae in cells A14, B14 and C14 by
highlighting all three cells and then pasting (ctrl-V)
the
original VLOOKUP formula into this selection.

Thanks for persisting with me on this one,
Ian.

-----Original Message-----
KL wrote:
Ian,

Just select 3 cells horizontally, copy the
formula . . .
Which formula? I gave two. One with commas, one with
semicolons. One
returns a vertical array, the other returns a
horizontal
array (which is
which depends on whether you use comma or semicolon as
your normal
delimiter).

Alan Beban

into the first one and
press Ctrl+Shift+Enter. Entering the same formula
vertically does not for me
either, but this does:

=TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6}))

again, select 3 cells vertically, enter the formula
in
the first cell and
Ctrl+Shift+Enter.

Regards,
KL



"Ian Johnson"
wrote in message
...

Alan thanks for your reply, however I'm struggling to
make
the function work. I follow the logic and it looks
like
what I want to do ie: fill three continuous cells in
a
row
from the VLOOKUP funtion. I've tried pasting your
example
in a blank cell and I get the value from column B,
but I
can't seem to get the values in columns E & F.
Presumably
I need to copy the VLOOKUP function in the first cell
into
the next two cells but I'm not sure how to get this
right,
the range reference increments as I copy it and
making
the
reference absolute just gets me the same value as the
first cell of the row.

Ian.

-----Original Message-----
Ian Johnson wrote:

Does the VLookUp function only allow you to return
the
value in ONE column from the "column_index_number"
in

the

functions arguements?
. . .

No.

=VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-
cell

row will return

the values from Columns B,E & F that correspond to
the

value of 3 in

Column A.

=VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-
cell

column.

Alan Beban
.




.



.