|
VLookUp function
Does the VLookUp function only allow you to return the
value in ONE column from the "column_index_number" in the functions arguements? If so, is it possible to return more than one columns value with another function? Many thanks Ian. |
VLookUp function
Do you mean within the same cell?
You could concatenate two vlookups referencing different columns. Ex: =VLOOKUP(E6,$I$8:$k$10,2,1)&" "&VLOOKUP(E6,$I$8:$k$10,3,1) tj "Ian Johnson" wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? If so, is it possible to return more than one columns value with another function? Many thanks Ian. |
VLookUp function
Ian Johnson wrote:
Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban |
VLookUp function
Alan thanks for your reply, however I'm struggling to make
the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban . |
VLookUp function
tj
Thanks for your comments but I'm actually looking to have a row of continuous cells display the result. Ian. -----Original Message----- Do you mean within the same cell? You could concatenate two vlookups referencing different columns. Ex: =VLOOKUP(E6,$I$8:$k$10,2,1)&" "&VLOOKUP(E6,$I$8:$k$10,3,1) tj "Ian Johnson" wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? If so, is it possible to return more than one columns value with another function? Many thanks Ian. . |
VLookUp function
Ian,
Just to build on Alan's reply - even if you don't enter the formula as an array one, it will still return the three values, although you will only be able to see the first one . However, the fact that you can't see them doesn't mean you can't manipulate the three results. To check this go to the formula bar, select the formula and press F9. For example: if your table is as follows A 2 5 8 D 1 11 20 P 4 1 9 E 1 30 21 =SUMPRODUCT(VLOOKUP("P",$A$1:$D$4,{2,3,4},FALSE)) this formula will sum up the resulting values and will give you 14 in this case. Regards, KL "Alan Beban" wrote in message ... Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban |
VLookUp function
Ian,
Just select 3 cells horizontally, copy the formula into the first one and press Ctrl+Shift+Enter. Entering the same formula vertically does not for me either, but this does: =TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6})) again, select 3 cells vertically, enter the formula in the first cell and Ctrl+Shift+Enter. Regards, KL "Ian Johnson" wrote in message ... Alan thanks for your reply, however I'm struggling to make the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban . |
VLookUp function
KL wrote:
Ian, Just select 3 cells horizontally, copy the formula . . . Which formula? I gave two. One with commas, one with semicolons. One returns a vertical array, the other returns a horizontal array (which is which depends on whether you use comma or semicolon as your normal delimiter). Alan Beban into the first one and press Ctrl+Shift+Enter. Entering the same formula vertically does not for me either, but this does: =TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6})) again, select 3 cells vertically, enter the formula in the first cell and Ctrl+Shift+Enter. Regards, KL "Ian Johnson" wrote in message ... Alan thanks for your reply, however I'm struggling to make the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban . |
VLookUp function
Wow! I didn't detect that difference. Thought they were identical and the
only difference was the direction of the list entry. Thanks, Alan "Alan Beban" wrote in message ... KL wrote: Ian, Just select 3 cells horizontally, copy the formula . . . Which formula? I gave two. One with commas, one with semicolons. One returns a vertical array, the other returns a horizontal array (which is which depends on whether you use comma or semicolon as your normal delimiter). Alan Beban into the first one and press Ctrl+Shift+Enter. Entering the same formula vertically does not for me either, but this does: =TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6})) again, select 3 cells vertically, enter the formula in the first cell and Ctrl+Shift+Enter. Regards, KL "Ian Johnson" wrote in message ... Alan thanks for your reply, however I'm struggling to make the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban . |
VLookUp function
Alan,
thanks for the reply but I'm still struggling. This is the data I'm trying this on: a b c d e f 0.457 3.55 500 200 200 200 0.525 3.25 400 195 195 195 0.616 2.93 300 190 190 190 0.675 2.75 250 185 185 185 0.746 2.57 200 180 180 180 0.835 2.38 150 175 175 175 0.946 2.17 100 170 170 170 1.09 1.95 50 165 165 165 1.29 1.71 0 160 160 160 and I'm using this VLOOKUP function: =VLOOKUP(1,A1:F10,{2,5,6}) but the result I get is: 2.17 #N/A #N/A when I would expect/hope for: 2.17 170 170 If I look at the VLOOKUP formulae in the three continuous cell (horizontally) they read: =VLOOKUP(1,A1:F10,{2,5,6}) then =VLOOKUP(1,B1:G10,{2,5,6}) and =VLOOKUP(1,C1:H10,{2,5,6}). I'm putting these formulae in cells A14, B14 and C14 by highlighting all three cells and then pasting (ctrl-V) the original VLOOKUP formula into this selection. Thanks for persisting with me on this one, Ian. -----Original Message----- KL wrote: Ian, Just select 3 cells horizontally, copy the formula . . . Which formula? I gave two. One with commas, one with semicolons. One returns a vertical array, the other returns a horizontal array (which is which depends on whether you use comma or semicolon as your normal delimiter). Alan Beban into the first one and press Ctrl+Shift+Enter. Entering the same formula vertically does not for me either, but this does: =TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6})) again, select 3 cells vertically, enter the formula in the first cell and Ctrl+Shift+Enter. Regards, KL "Ian Johnson" wrote in message ... Alan thanks for your reply, however I'm struggling to make the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban . . |
VLookUp function
Select your three cells
go to the formula bar and enter =VLOOKUP(1,A1:F10,{2,5,6}) Now, normally you would hit Enter to finish the editing, but instead you should hold down the ctrl and Shift keys, then while holding them down, hit the enter key as well. Ctrl+Shift+Enter no select one of the cells (the formula should be identical in all cells rather than shifted as you show). {=VLOOKUP(1,A1:F10,{2,5,6})} the formula will appear to be in brackets as shown above. these are not actually present, but they are used by excel to give feedback that the formula has been entered as and is being treated as an array formula. If you now try to edit any single of those 3 cells, you will get an error that you can not edit that cell. You would need to again select all 3 cells and do the edit, then again close out with Ctrl+Shift+Enter. Now if you want to copy down as an example, you would select all three cells, then drag fill down. If this is not clear to you, then reread the thread and perhaps you will understand one of the other explanations that have given similar advice. -- Regards, Tom Ogilvy wrote in message ... Alan, thanks for the reply but I'm still struggling. This is the data I'm trying this on: a b c d e f 0.457 3.55 500 200 200 200 0.525 3.25 400 195 195 195 0.616 2.93 300 190 190 190 0.675 2.75 250 185 185 185 0.746 2.57 200 180 180 180 0.835 2.38 150 175 175 175 0.946 2.17 100 170 170 170 1.09 1.95 50 165 165 165 1.29 1.71 0 160 160 160 and I'm using this VLOOKUP function: =VLOOKUP(1,A1:F10,{2,5,6}) but the result I get is: 2.17 #N/A #N/A when I would expect/hope for: 2.17 170 170 If I look at the VLOOKUP formulae in the three continuous cell (horizontally) they read: =VLOOKUP(1,A1:F10,{2,5,6}) then =VLOOKUP(1,B1:G10,{2,5,6}) and =VLOOKUP(1,C1:H10,{2,5,6}). I'm putting these formulae in cells A14, B14 and C14 by highlighting all three cells and then pasting (ctrl-V) the original VLOOKUP formula into this selection. Thanks for persisting with me on this one, Ian. -----Original Message----- KL wrote: Ian, Just select 3 cells horizontally, copy the formula . . . Which formula? I gave two. One with commas, one with semicolons. One returns a vertical array, the other returns a horizontal array (which is which depends on whether you use comma or semicolon as your normal delimiter). Alan Beban into the first one and press Ctrl+Shift+Enter. Entering the same formula vertically does not for me either, but this does: =TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6})) again, select 3 cells vertically, enter the formula in the first cell and Ctrl+Shift+Enter. Regards, KL "Ian Johnson" wrote in message ... Alan thanks for your reply, however I'm struggling to make the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3-cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3-cell column. Alan Beban . . |
VLookUp function
Thanks Tom for making it so clear, it all works now.
Ian. -----Original Message----- Select your three cells go to the formula bar and enter =VLOOKUP(1,A1:F10,{2,5,6}) Now, normally you would hit Enter to finish the editing, but instead you should hold down the ctrl and Shift keys, then while holding them down, hit the enter key as well. Ctrl+Shift+Enter no select one of the cells (the formula should be identical in all cells rather than shifted as you show). {=VLOOKUP(1,A1:F10,{2,5,6})} the formula will appear to be in brackets as shown above. these are not actually present, but they are used by excel to give feedback that the formula has been entered as and is being treated as an array formula. If you now try to edit any single of those 3 cells, you will get an error that you can not edit that cell. You would need to again select all 3 cells and do the edit, then again close out with Ctrl+Shift+Enter. Now if you want to copy down as an example, you would select all three cells, then drag fill down. If this is not clear to you, then reread the thread and perhaps you will understand one of the other explanations that have given similar advice. -- Regards, Tom Ogilvy wrote in message ... Alan, thanks for the reply but I'm still struggling. This is the data I'm trying this on: a b c d e f 0.457 3.55 500 200 200 200 0.525 3.25 400 195 195 195 0.616 2.93 300 190 190 190 0.675 2.75 250 185 185 185 0.746 2.57 200 180 180 180 0.835 2.38 150 175 175 175 0.946 2.17 100 170 170 170 1.09 1.95 50 165 165 165 1.29 1.71 0 160 160 160 and I'm using this VLOOKUP function: =VLOOKUP(1,A1:F10,{2,5,6}) but the result I get is: 2.17 #N/A #N/A when I would expect/hope for: 2.17 170 170 If I look at the VLOOKUP formulae in the three continuous cell (horizontally) they read: =VLOOKUP(1,A1:F10,{2,5,6}) then =VLOOKUP(1,B1:G10, {2,5,6}) and =VLOOKUP(1,C1:H10,{2,5,6}). I'm putting these formulae in cells A14, B14 and C14 by highlighting all three cells and then pasting (ctrl-V) the original VLOOKUP formula into this selection. Thanks for persisting with me on this one, Ian. -----Original Message----- KL wrote: Ian, Just select 3 cells horizontally, copy the formula . . . Which formula? I gave two. One with commas, one with semicolons. One returns a vertical array, the other returns a horizontal array (which is which depends on whether you use comma or semicolon as your normal delimiter). Alan Beban into the first one and press Ctrl+Shift+Enter. Entering the same formula vertically does not for me either, but this does: =TRANSPOSE(VLOOKUP(3,A1:G8,{2;5;6})) again, select 3 cells vertically, enter the formula in the first cell and Ctrl+Shift+Enter. Regards, KL "Ian Johnson" wrote in message ... Alan thanks for your reply, however I'm struggling to make the function work. I follow the logic and it looks like what I want to do ie: fill three continuous cells in a row from the VLOOKUP funtion. I've tried pasting your example in a blank cell and I get the value from column B, but I can't seem to get the values in columns E & F. Presumably I need to copy the VLOOKUP function in the first cell into the next two cells but I'm not sure how to get this right, the range reference increments as I copy it and making the reference absolute just gets me the same value as the first cell of the row. Ian. -----Original Message----- Ian Johnson wrote: Does the VLookUp function only allow you to return the value in ONE column from the "column_index_number" in the functions arguements? . . . No. =VLOOKUP(3,A1:G8,{2,5,6}), array entered into a 3- cell row will return the values from Columns B,E & F that correspond to the value of 3 in Column A. =VLOOKUP(3,A1:G8,{2;5;6}) will return them to a 3- cell column. Alan Beban . . . |
| All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com