Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP change question
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP change question
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP change question
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP change question
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP change question
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP change question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP question | Excel Worksheet Functions | |||
vlookup question | Excel Worksheet Functions | |||
A question about VLOOKUP / any other helpful formula | Excel Worksheet Functions | |||
VLookUp Question | Excel Discussion (Misc queries) | |||
Vlookup question | Excel Worksheet Functions |