Hello,

Am I able to reference three cells using a vlookup?

i would like to join the cells similiar to a formula that uses &

Any help?

Thanks

You overwhelmed us with information ! <g

Do you mean something like this:

=VLOOKUP(...)&VLOOKUP(...)&VLOOKUP(...)

--
Biff
Microsoft Excel MVP


"Jim" wrote in message
...
Hello,

Am I able to reference three cells using a vlookup?

i would like to join the cells similiar to a formula that uses &

Any help?

Thanks

Hi Jim,

Do you want to reference three cells as the lookup values and return a
single value or do you want to return three different cells value from a
single lookup value.

I believe either is possible, either with SUMPRODUCT for the first instance
(Maybe with a Pivot Table but I'm weak on that) or and array entered VLOOKUP
for the second.

I think I can get the second instance to work with a formula something like
this:

=VLOOKUP(A1,B1:E10,{2,3,4},0)

Where you select three cells in the same row and while still selected enter
the formula above, then commit with Ctrl + Shift + Enter. Excel will put
curly bracket around the formula, don't enter them yourself.

A1 = The lookup value. (Could be from a drop down list in A1)
B1:B10 = a list of the lookup values.
C1:E10 contains the info you want to return for that lookup value.

If you need to change the lookup value cell or the lookup range, you will
need to select those three cells again and make the changes and commit with
Ctrl + Shift + Enter again.

HTH
Regards,
Howard

"Jim" wrote in message
...
Hello,

Am I able to reference three cells using a vlookup?

i would like to join the cells similiar to a formula that uses &

Any help?

Thanks

Hi Jim

When you have multiple cells to be looked up you can try the array formula
using INDEX() MATCH()....

Col A Col B Col C
Rank Name Office
1 Tom R Bath
2 Katy C Bath
3 Nigel G Bath
4 Pete R Bath
1 Tony A London
2 John B London
3 Mary C London
4 Jane D London

In the above example if you want to find out the name of the person holding
first rank from London..Please note that this is an array formula. You create
array formulas in the same way that you create other formulas, except you
press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'
you can notice the curly braces at both ends like "{=<formula}"

=INDEX($B$1:$B$10,MATCH(1,($A$1:$A$10=1)*($C$1:$C$ 10="London"),0))

--
Jacob


"Jim" wrote:

Hello,

Am I able to reference three cells using a vlookup?

i would like to join the cells similiar to a formula that uses &

Any help?

Thanks