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. It puts a list of things to do in the "actions" column cell, each list different, based on what is picked from pulldown "items" column cell.

But now, 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. Either an x or an o will go in the cells based on what is picked in pulldown. The "action" names will now be in the title row.

How would I do that?

[CLR]

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



"roger_home" wrote:

>
> 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?
>
>
> --
> roger_home
> ------------------------------------------------------------------------
> roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434
> View this thread: http://www.excelforum.com/showthread...hreadid=516529
>
>

[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.



roger_home wrote:
>
> 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?
>
> --
> roger_home
> ------------------------------------------------------------------------
> roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434
> View this thread: http://www.excelforum.com/showthread...hreadid=516529

--

Dave Peterson

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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?
>
>
> --
> roger_home
> ------------------------------------------------------------------------
> roger_home's Profile:
> http://www.excelforum.com/member.php...o&userid=16434
> View this thread: http://www.excelforum.com/showthread...hreadid=516529
>

[roger_home]

I did what Dave Peterson said and it works fine for the 1st column, on the 2nd, 3rd, etc. I get #ref!. (I was carefull to make a new VLOOKUP 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.

[email protected]

Howard

"roger_home" <[email protected]> wrote
in message news:[email protected]...
>
> 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?
>
>
> --
> roger_home
> ------------------------------------------------------------------------
> roger_home's Profile:
> http://www.excelforum.com/member.php...o&userid=16434
> View this thread: http://www.excelforum.com/showthread...hreadid=516529
>

[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.



roger_home wrote:
>
> I did what Dave Peterson said and it works fine for the 1st column, on
> the 2nd, 3rd, etc. I get n/a. (I was carefull to make a new VLOOKUP
> range and name it different.) Any ideas??
>
> --
> roger_home
> ------------------------------------------------------------------------
> roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434
> View this thread: http://www.excelforum.com/showthread...hreadid=516529

--

Dave Peterson