Vlookup - Return Multiple Values Across Columns

[Dubyah]

Hey All,

Got a quick question.. I have two sheets in which I'm trying to combine. The only constant is the URL.. I'd like to bring 7 columns of data over from the 'table' into the master sheet. Any way to do this without writing a new function in each column? Absolute values are cool, too I guess.

I've seen that this can be done by making the vlookup an array function.. But I cant seem to get it to work on my mac.

Any pointers are greatly appreciated, tried my hand at searching the forum but no luck.

 

[AFPathfinder]

To my knowledge, I don't believe there is a function that will fill multiple cells by itself. If there is, it would probably be in the INDEX/MATCH functions or using the advanced filter.

Clever use of relative and nonrelative references in a VLOOKUP function along with dragging the fill handle across the 7 columns can make fairly quick work of the task. You'd need to adjust the "col_index_num" property for each or make good use of the COLUMN() function.

 

[Joe4]

Formulas can only return values to the cells that they are located in (they cannot return values to other cells).
So, unless you use VBA, you are going to need formulas in all the cells you want to return values.

Clever use of relative and nonrelative references in a VLOOKUP function along with dragging the fill handle across the 7 columns can make fairly quick work of the task. You'd need to adjust the "col_index_num" property for each or make good use of the COLUMN() function.

Yep, that is what I would recommend.

If you are running into issues, provide your actual formula and pertinent details (i.e. what columns the formulas are being placed in).

 

[Dubyah]

Hey Guys,

Thanks for the responses I was able to figure it out on my own with a little finagling. Appreciate your feedback. Here's what I came up with AKA clever use of relative and non-relative references. Sheet2 had over 30k rows so this really did the trick >.<

[B][SIZE=2][FONT=arial]=VLOOKUP($Q2,Sheet2!$A$2:$G$1185,COLUMN(Sheet2!B2),FALSE)[/FONT][/SIZE][/B]