VLookup that returns two separate cells instead of only one
 

Hello,

I have a table of 76 items (A2:D77). I want to key the item code into cell
F2 (of the same worksheet) and have Excel populate two pieces of data to the
right of F2 (this would be G2 and H2), that corresponds to the item code
typed in cell F2.

The original data is contained in A2:D77 so I have no problem doing this
when I use Vlookup but the formula only returns to data in the column I
specify, which is one column. I don't know how to write the formula to say I
want two pieces of data that correspond to "the value I enter" in the Vlookup.

I am sure what I've typed above is probably as clear as muddy water...

Thanks for any help you can offer, it is greatly appreciated.

VLookup that returns two separate cells instead of only one
 

You are right, it is muddy :-)

Try this in G2
=VLOOKUP(F2,A2:D77,2,False) to get the value corresponding to the value
equal to F2 from the second column (B).

If you want the valued from the third colum in H2 then use
=VLOOKUP(F2,A2:D77,3,False)

If you want both in the same cell then use
=VLOOKUP(F2,A2:D77,2,False) & VLOOKUP(F2,A2:D77,3,False)

or

=VLOOKUP(F2,A2:D77,2,False) & " " & VLOOKUP(F2,A2:D77,3,False) if you want a
space in between

Does this makes sense?

"Jake" wrote:

Hello,

I have a table of 76 items (A2:D77). I want to key the item code into cell
F2 (of the same worksheet) and have Excel populate two pieces of data to the
right of F2 (this would be G2 and H2), that corresponds to the item code
typed in cell F2.

The original data is contained in A2:D77 so I have no problem doing this
when I use Vlookup but the formula only returns to data in the column I
specify, which is one column. I don't know how to write the formula to say I
want two pieces of data that correspond to "the value I enter" in the Vlookup.

I am sure what I've typed above is probably as clear as muddy water...

Thanks for any help you can offer, it is greatly appreciated.

VLookup that returns two separate cells instead of only one
 

It depends on what columns in the table you want the results from.

If you want results from adjacent columns:

Results from columns C & D:

Entered in G2 and copied across to H2:

=VLOOKUP($F2,$A$2:$D$77,COLUMNS($A2:C2),0)

Results from columns B & C:

Entered in G2 and copied across to H2:

=VLOOKUP($F2,$A$2:$D$77,COLUMNS($B2:C2),0)

For results from non-adjacent columns:

Results from columns B & D:

Entered in G2 and copied across to H2:

=VLOOKUP($F2,$A$2:$D$15,2*COLUMNS($G2:G2),0)

--
Biff
Microsoft Excel MVP


"Jake" wrote in message
...
Hello,

I have a table of 76 items (A2:D77). I want to key the item code into
cell
F2 (of the same worksheet) and have Excel populate two pieces of data to
the
right of F2 (this would be G2 and H2), that corresponds to the item code
typed in cell F2.

The original data is contained in A2:D77 so I have no problem doing this
when I use Vlookup but the formula only returns to data in the column I
specify, which is one column. I don't know how to write the formula to
say I
want two pieces of data that correspond to "the value I enter" in the
Vlookup.

I am sure what I've typed above is probably as clear as muddy water...

Thanks for any help you can offer, it is greatly appreciated.

VLookup that returns two separate cells instead of only one
 

I think you are going to have to enter a vlookup in each cell where you are
wanting information returned. For example, in g2, enter

vlookup(f2,a2:d77,2,0),

then in h2 enter

vlookup(f2,a2:d77,3,0)

one other helpful hint would be to name your range (a2:d77) (lets say info
for example). That way, you can type vlookup(f2,info,2,0) and then copy the
formula down to other cells.


"Jake" wrote in message
...
Hello,

I have a table of 76 items (A2:D77). I want to key the item code into
cell
F2 (of the same worksheet) and have Excel populate two pieces of data to
the
right of F2 (this would be G2 and H2), that corresponds to the item code
typed in cell F2.

The original data is contained in A2:D77 so I have no problem doing this
when I use Vlookup but the formula only returns to data in the column I
specify, which is one column. I don't know how to write the formula to
say I
want two pieces of data that correspond to "the value I enter" in the
Vlookup.

I am sure what I've typed above is probably as clear as muddy water...

Thanks for any help you can offer, it is greatly appreciated.