VLOOKUP issue

B

Barrym

If I use a vlookup function, can I have three result cells? In othe
words, if the value in a1 is found in the lookup table and there ar
three columns (offsets) that could be used as correct answers, can
display all three
 
P

Peo Sjoblom

Yes, you can use

=VLOOKUP(A1,Lookup_Table,{2,3,4},0)

assuming you don't want the value from the leftmost column, you need to
select 3 columns across
(like if you would select E2:G2 with E2 as the active cell), then enter the
formula in the formula bar and instead pressing enter press ctrl + shift &
enter

or you could use

=VLOOKUP($A$1,Lookup_Table,COLUMN(B:B),0)

put that in one cell and copy it across 2 more cells

note that I made the lookup value absolute or else it would change from A1
to B1 etc

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
D

Domenic

Barrym said:
*If I use a vlookup function, can I have three result cells? In othe
words, if the value in a1 is found in the lookup table and there ar
three columns (offsets) that could be used as correct answers, can
display all three? *
Click to expand...

Hi,

As an example, if you have the following table on Sheet 1,

Sheet 1
---------

Red 1 2 3
Blue 4 5 6
Green 7 8 9

and you want to return mutliple results for a lookup value, say Blue
then on Sheet 2, assuming that your lookup value is in A1, selec
B1:D1, and enter the following array formula:

=VLOOKUP(A1,Sheet1!$A$1:$D$3,{2,3,4})

to be entered using CTRL+SHIFT+ENTER, and you'll get the followin
results:

Sheet 2
 
B

Barrym

Barrym said:
*If I use a vlookup function, can I have three result cells? In othe
words, if the value in a1 is found in the lookup table and there ar
three columns (offsets) that could be used as correct answers, can
display all three? *
Click to expand...

OK I'm a bit thick but I can't get this to work!

Suggestions
 
D

Domenic

Ok, first layout a table on Sheet 1 like the one I gave for an example.

So,

A1=Red
B1=1
C1=2
D1=3

and continue to complete the table.

Then switch over to Sheet 2 and enter Blue in A1.

Then select cells B1 to D1. So you now have three cells highlight, an
Blue as your lookup value in A1.

Press = and enter this formula:

=VLOOKUP(A1,Sheet1!$A$1:$D$3,{2,3,4},0)

Then press, altogether, the following keys: CTRL+SHIFT+ENTER

Does this help
 
Top