Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
I have worksheet A with 5 columns of information and 10 rows.
I have worksheet B with just one column of information and multiple rows. Column A in both worksheets are codes. I want to do a function that will look at col A, find the match to it on worksheet B and then return columns B-G on the same line that it matched column A on. I know this sounds fuzzy but I can return one value (i.e. column B2, but not columns b2 through 6g Any help would be terrific. I tried to use the vlookup function but got messed up when I went to reference columns b through g. Thank you! Susan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
Hi Susan,
try changing the col_index_num and pasting the formula in the next column and copy down. In worksheet B, In column b: =VLOOKUP(SheetA!$A$1,SheetA!$A$1:$F$10,2,FALSE) Column C put: =VLOOKUP(SheetA!$A$1,SheetA!$A$1:$F$10,3,FALSE) Column D put: =VLOOKUP(SheetA!$A$1,SheetA!$A$1:$F$10,3,FALSE) etc. Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
SueB,
I think dan dungan has probably given you the fix you need. I suspect that in your original VLOOKUP() you probably didn't reference the entire matrix. You may have written something like: =VLOOKUP(Sheet1!A1,Sheet1!$A$1:B$6,2,False) That would work to just pick up values from column B in the matrix, but you could not set the column reference (,2,) to any number greater than that because you have only referenced 2 columns in the matrix. Notice that in dan's formulas, he referenced all the way over to column F, so he can use any number from 1 to 6 to get the information from the various columns in it, since it has 6 columns in the matrix. "SueB" wrote: I have worksheet A with 5 columns of information and 10 rows. I have worksheet B with just one column of information and multiple rows. Column A in both worksheets are codes. I want to do a function that will look at col A, find the match to it on worksheet B and then return columns B-G on the same line that it matched column A on. I know this sounds fuzzy but I can return one value (i.e. column B2, but not columns b2 through 6g Any help would be terrific. I tried to use the vlookup function but got messed up when I went to reference columns b through g. Thank you! Susan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
You can use an incrementer for the column index param in the vlookup to
facilitate easy copying across from the point formula cell, eg: COLUMNS($A:A)+1 Something along these lines, based on your descript, should work fine In sheet: B, Put in B2: =VLOOKUP($A2,A!$A:$G,COLUMNS($A:A)+1,0) Copy B2 to G2, fill down as far as required And if you need an error trap to return blanks: "" for any unmatched cases, put this instead in B2: =IF(ISNA(VLOOKUP($A2,A!$A:$G,COLUMNS($A:A)+1,0))," ",VLOOKUP($A2,A!$A:$G,COLUMNS($A:A)+1,0)) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "SueB" wrote: I have worksheet A with 5 columns of information and 10 rows. I have worksheet B with just one column of information and multiple rows. Column A in both worksheets are codes. I want to do a function that will look at col A, find the match to it on worksheet B and then return columns B-G on the same line that it matched column A on. I know this sounds fuzzy but I can return one value (i.e. column B2, but not columns b2 through 6g Any help would be terrific. I tried to use the vlookup function but got messed up when I went to reference columns b through g. Thank you! Susan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
SueB wrote:
I have worksheet A with 5 columns of information and 10 rows. I have worksheet B with just one column of information and multiple rows. Column A in both worksheets are codes. I want to do a function that will look at col A, find the match to it on worksheet B and then return columns B-G on the same line that it matched column A on. I know this sounds fuzzy but I can return one value (i.e. column B2, but not columns b2 through 6g Any help would be terrific. I tried to use the vlookup function but got messed up when I went to reference columns b through g. Thank you! Susan =VLOOKUP(sought_value, A1:G10, {2,3,4,5,6,7},False) array entered into a horizontal range of 6 cells. Alan Beban |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
I just wanted to thank you Dan and everyone who responded. Your solution
worked great! I detest duplication of effort and we were hand entering values into a spreadsheet from a report that could have been imported into excel. We were just missing the data from standard columns. This saves us so much work. Thanks everyone! Susan "dan dungan" wrote: Hi Susan, try changing the col_index_num and pasting the formula in the next column and copy down. In worksheet B, In column b: =VLOOKUP(SheetA!$A$1,SheetA!$A$1:$F$10,2,FALSE) Column C put: =VLOOKUP(SheetA!$A$1,SheetA!$A$1:$F$10,3,FALSE) Column D put: =VLOOKUP(SheetA!$A$1,SheetA!$A$1:$F$10,3,FALSE) etc. Dan |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
Wow.
I just realized another reason why it's good to help out in these newsgroups. Thanks Alan Dan |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning multiple cell values from a vlookup
dan dungan wrote:
Wow. I just realized another reason why it's good to help out in these newsgroups. Thanks Alan Dan De nada. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup - returning multiple vertical values | Excel Discussion (Misc queries) | |||
Using VLOOKUP for returning multiple values and summing them | Excel Worksheet Functions | |||
VLookup returning the sum of multiple values from one "code" | Excel Worksheet Functions | |||
vlookup, multiple values, sum values into one cell?? | Excel Worksheet Functions | |||
vlookup returning multiple values | Excel Worksheet Functions |