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

If so, is it possible to return more than one columns
value with another function?

Many thanks
Ian.

Do you mean within the same cell?
You could concatenate two vlookups referencing different columns.
Ex:
=VLOOKUP(E6,$I$8:$k$10,2,1)&" "&VLOOKUP(E6,$I$8:$k$10,3,1)

tj

"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?

If so, is it possible to return more than one columns
value with another function?

Many thanks
Ian.

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

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
.

tj

Thanks for your comments but I'm actually looking to have
a row of continuous cells display the result.

Ian.
-----Original Message-----
Do you mean within the same cell?
You could concatenate two vlookups referencing different
columns.
Ex:
=VLOOKUP(E6,$I$8:$k$10,2,1)&" "&VLOOKUP(E6,$I$8:$k$10,3,1)

tj

"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?

If so, is it possible to return more than one columns
value with another function?

Many thanks
Ian.

.

Ian,

Just to build on Alan's reply - even if you don't enter the formula as an
array one, it will still return the three values, although you will only be
able to see the first one . However, the fact that you can't see them
doesn't mean you can't manipulate the three results. To check this go to the
formula bar, select the formula and press F9. For example:

if your table is as follows
A 2 5 8
D 1 11 20
P 4 1 9
E 1 30 21

=SUMPRODUCT(VLOOKUP("P",$A$1:$D$4,{2,3,4},FALSE))

this formula will sum up the resulting values and will give you 14 in this
case.

Regards,
KL

"Alan Beban" wrote in 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

Ian,

Just select 3 cells horizontally, copy the formula 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
.

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
.

Wow! I didn't detect that difference. Thought they were identical and the
only difference was the direction of the list entry.

Thanks,
Alan

"Alan Beban" wrote in 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
.

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
.




.

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
.




.

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
.




.



.