vlookup and multiple columns

[Guest]

Hello
I've looked thru the discussion groups about my question...and I think I have half of it answered (from a posting on 4/15/2004). I understand that to fill multiple columns based on a lookup for one cell...you use =VLOOKUP(A1,A10:F100,{2,3,4,5,6},0) and it will input the info into those 5 columns in the same order as your original table. Now, can you expand this formula to other rows without having to go thru each row and hitting "ctrl shift enter" each time? (I have 5000 rows) I've tried dragging it down, that doesn't work. And I've tried highlighting the entire worksheet and hitting "CSE" and that doesn't work either. Any suggestions? Is there a better way to do this? Thanks for your help

 

[DDM]

ST, I'm going to assume the formula works for you. I just want to address
the question of "expanding it."

If you change the table_array reference (here A10:F100) from relative to
absolute ($A$10:$F$100), you'll be able to drag the formula down and it
should work.
--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

Hello,
I've looked thru the discussion groups about my question...and I think I

have half of it answered (from a posting on 4/15/2004). I understand that
to fill multiple columns based on a lookup for one cell...you use
=VLOOKUP(A1,A10:F100,{2,3,4,5,6},0) and it will input the info into those 5
columns in the same order as your original table. Now, can you expand this
formula to other rows without having to go thru each row and hitting "ctrl
shift enter" each time? (I have 5000 rows) I've tried dragging it down,
that doesn't work. And I've tried highlighting the entire worksheet and
hitting "CSE" and that doesn't work either. Any suggestions? Is there a
better way to do this? Thanks for your help.

 

[Alan Beban]

Hello,
I've looked thru the discussion groups about my question...and I think I have half of it answered (from a posting on 4/15/2004). I understand that to fill multiple columns based on a lookup for one cell...you use =VLOOKUP(A1,A10:F100,{2,3,4,5,6},0) and it will input the info into those 5 columns in the same order as your original table. Now, can you expand this formula to other rows without having to go thru each row and hitting "ctrl shift enter" each time? (I have 5000 rows) I've tried dragging it down, that doesn't work. And I've tried highlighting the entire worksheet and hitting "CSE" and that doesn't work either. Any suggestions? Is there a better way to do this? Thanks for your help.

Well, you started a new thread so we can't tell what your question is.
Are you trying to sum something? Are you dealing with multiple rows with
the same lookup value? Something else?

Alan Beban

 

[Guest]

Sorry, I meant to say I had a question in my head...and I was able to find half the answer thru other postings, ie, that formula with some variation (=VLOOKUP(A1,A10:F100,{2,3,4,5,6},0) ) but it works for the first row of data, but I can't drag it down to work for all the rows...I even tried the $ suggestion and it didn't work. Is there a better way to do this?

Basically, I have data in 2 separate sheets, and I need to transfer the date from one, to the other
Sheet 1 (the one with the data
Subject ID LName FName Phon
1 Smith Bob 555-555
2 Jones Joe 444-444
3 Mark Marky 333-333

Sheet 2 (the one that needs the data filled in
Subject ID LName FName Phon









So...for wherever there's a 1...it'll put the info from Sheet 1 there, same with 2, 3, etc. Now I have 5000 Subject Id's...and about 200 columns (which is why I don't want to do a separate VLookup for each column). Any ideas? Thanks so much

 

[DDM]

ST, try this one:

=VLOOKUP($A1,Sheet1!$A$10:$F$100,COLUMN(),FALSE).

Put it in the first cell; copy across, then copy down.

If the table_array argument ($A$10:$F$100) doesn't work (and it should, so
long as the cell references are correct and the table is sorted ascending on
Column A), you can select the data, give it a name, and use that name in the
formula, thus:

=VLOOKUP($A2,MyData,COLUMN(),FALSE).
--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

Sorry, I meant to say I had a question in my head...and I was able to find

half the answer thru other postings, ie, that formula with some variation
(=VLOOKUP(A1,A10:F100,{2,3,4,5,6},0) ) but it works for the first row of
data, but I can't drag it down to work for all the rows...I even tried the $
suggestion and it didn't work. Is there a better way to do this?

Basically, I have data in 2 separate sheets, and I need to transfer the date from one, to the other:
Sheet 1 (the one with the data)
Subject ID LName FName Phone
1 Smith Bob 555-5555
2 Jones Joe 444-4444
3 Mark Marky 333-3333

Sheet 2 (the one that needs the data filled in)
Subject ID LName FName Phone
1
1
2
3
2
1
1
3

So...for wherever there's a 1...it'll put the info from Sheet 1 there,

same with 2, 3, etc. Now I have 5000 Subject Id's...and about 200 columns
(which is why I don't want to do a separate VLookup for each column). Any
ideas? Thanks so much!