VLOOKUP change question

[roger_home]

Someone on this site gave me (a newbie) the following to fill in a cell
in a row if another cell in the same row has an item picked from a
pulldown I have. (The list for the pulldown is on another sheet).

=VLOOKUP(D471,MYLOOKUP,2,0)

It works fine, but now instead of filling the VLOOKUP items (12 words)
in the cell, I would like to have 12 separate items put into 12
separate cells in that same row, when an item is picked from the
pulldown.

How would I do that?

 

[Guest]

The Data > TextToColumns feature will split the 12 words into 12 columns, if
that's what you mean.......or, if you want to return 12 separate cells with
VLOOKUP, then you would need a separate VLOOKUP formula in each cell to
return that one value,....the difference being to increment the "2" in your
original formula to 3,4,5,....etc....

hth
Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

Can you make your MYLOOKUP table have the one key column and then 12 more
columns with a single word in each cell?

Then you could just use 12 of those =vlookup()'s to get your 12 different words:

=VLOOKUP(D471,MYLOOKUP,2,0)
=VLOOKUP(D471,MYLOOKUP,3,0)
=VLOOKUP(D471,MYLOOKUP,4,0)
=VLOOKUP(D471,MYLOOKUP,5,0)
....
=VLOOKUP(D471,MYLOOKUP,12,0)
=VLOOKUP(D471,MYLOOKUP,13,0)


Remember to may that MYLOOKUP table 13 columns wide.

 

[L. Howard Kittle]

One way. Select the 12 cells you want the returned values to be in and
while still selected enter this formula. Then array enter CTRL-SHIFT-ENTER.
You will get { } around the formula.

=VLOOKUP(A2,A5:L30,{2,3,4,5,6,7,8,9,10,11,12,13},0)

Adjust the lookup value cell A2 and the lookup range to match your sheet.
Once entered, if you make any changes to the formula, you will need to
select all 12 cells and then make the change in the "white" cell and array
enter again.

HTH
Regards,
Howard

 

[roger_home]

I did what Dave Peterson said and it works fine for the 1st column, o
the 2nd, 3rd, etc. I get n/a. (I was carefull to make a new VLOOKU
range and name it different.) Any ideas?

 

[L. Howard Kittle]

Hmmm? If you want, send me a sample workbook and I will take a look at it.

(e-mail address removed)

Howard

 

[Dave Peterson]

If you dragged the formula across the columns, then that first reference may
have changed:

=VLOOKUP(D471,MYLOOKUP,2,0)

did it change to something like:
=VLOOKUP(E471,MYLOOKUP,3,0)

You may want to do:
=vlookup($d471,mylookup2,2,0)

Then when you drag it across the columns, the column (in $d471) will not change.