returning multiple cell values from a vlookup

[SueB]

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

 

[dan dungan]

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

 

[JLatham]

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.

 

[Max]

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

 

[Alan Beban]

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

 

[SueB]

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]

Wow.

I just realized another reason why it's good to help out in these
newsgroups.

Thanks Alan

Dan

 

[Alan Beban]

Wow.

I just realized another reason why it's good to help out in these
newsgroups.

Thanks Alan

Dan

De nada.