In VLOOKUP() can one "fetch" a range of columns?
 

2003

With VLOOKUP() I know that one can retrieve a R,C data item for the Found row.

What is the best way to retrieve a range of columnar data if the Keys match?

Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False)

If not what is the best approach both with Excel and XL VBA?

TIA EagleOne

In VLOOKUP() can one "fetch" a range of columns?
 

I should have made this point:

If a match, I would like to return all columnar data items on Sheet2 to columns in the same row but
different columns on Sheet1

wrote:

2003

With VLOOKUP() I know that one can retrieve a R,C data item for the Found row.

What is the best way to retrieve a range of columnar data if the Keys match?

Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False)

If not what is the best approach both with Excel and XL VBA?

TIA EagleOne

In VLOOKUP() can one "fetch" a range of columns?
 

You really need a vlookup for each cell that will contain data (i.e.: 22
cells in each row will have data or 21 if not including the key, no matter
the order, you will need a formula for each cell).
--
John C


" wrote:

I should have made this point:

If a match, I would like to return all columnar data items on Sheet2 to columns in the same row but
different columns on Sheet1

wrote:

2003

With VLOOKUP() I know that one can retrieve a R,C data item for the Found row.

What is the best way to retrieve a range of columnar data if the Keys match?

Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False)

If not what is the best approach both with Excel and XL VBA?

TIA EagleOne

In VLOOKUP() can one "fetch" a range of columns?
 

I kind of figured that. This is a large w/s and I was attempting to avoid all of the
"Formula-overhead"

Thanks


John C <johnc@stateofdenial wrote:

You really need a vlookup for each cell that will contain data (i.e.: 22
cells in each row will have data or 21 if not including the key, no matter
the order, you will need a formula for each cell).

In VLOOKUP() can one "fetch" a range of columns?
 

I think what I'll do is
1) place the "found Sheet2" row-reference in a column on Sheet 2
2) return that value to Sheet1
3) then use VBA to copy/paste the resprctive Row/Cols found-range from Sheet2 to Sheet1

John C <johnc@stateofdenial wrote:

You really need a vlookup for each cell that will contain data (i.e.: 22
cells in each row will have data or 21 if not including the key, no matter
the order, you will need a formula for each cell).

In VLOOKUP() can one "fetch" a range of columns?
 

Hi EagleOne,

Try this, if I read your post correctly. This is a four column return from
the look value, adjust to do 22 columns by adding 6 to 22 in the {}'s inside
the formula.

You will need to SELECT all the cells you want the returns to be in and
while selected, enter the following formula in the selected white cell, all
others remain highlighted as you do this. Then array enter, Ctrl + Shift +
Enter. This will produce {}'s around the formula, don't enter them
yourself.

=VLOOKUP(A1,Sheet2!A1:E3,{2,3,4,5},0)

If you need to modify the formula later you will need to select all the
cells that have the formulas, do your modification and again array enter
with Ctrl+Shift+Enter. To delete the formulas, again select all cells and
hit Delete and then array enter again.

HTH
Regards,
Howard

wrote in message
...
2003

With VLOOKUP() I know that one can retrieve a R,C data item for the Found
row.

What is the best way to retrieve a range of columnar data if the Keys
match?

Conceptually i.e VLOOKUP(A1,Stars!A1:Z200, Columns 1 thru 22,False)

If not what is the best approach both with Excel and XL VBA?

TIA EagleOne