Need V-Lookup to Return A Row, Not Just Single Value

[PeteT.]

I have a spreadsheet with about 50 columns and up to several hundred rows
with column A containing the Team Name.
I have a simple table setup with each of the team names on one tab of a
sheet...on the 2nd tab, I was trying to run a v-lookup to return up to the
first 50 of each team name along with the data from first 22 columns....

The formula is/was
=vlookup(TeamTableA!6,'[DataSheet.xls]Sheet1'!$A:$V,{1,2,3,etc up to
22},FALSE)

I figured the array {1,2,3} would return the first 22 columns in the
row....but apparently not..

I also only want to return up to the first 50 instances of each team name.
I can figure out how to make each unique if need be but any help on getting
the lookup to return the first 22 columns in the row would be appreciated.

 

[Jim Thomlinson]

A few choices.

1 - you need to have the individul column references specified (In the
formula column referencing. This is time consuming because you need to modify
each formula)

2 - Use the column() function allowing you to drag your formula across
columns and hvae the return column reference increment. This will make your
formulas volatile and add a lot of calculation overhead (not a good idea)

3 - Use an external range to hold the column references. Place 1 , 2, 3 ...
in a hidden row and refer to those cells in your formula. This works but it
is not necessary.

4 - Index / Match functions. This is probably the best option.
=index('[DataSheet.xls]Sheet1'!B:B, match(TeamTable!$A$6,
'[DataSheet.xls]Sheet1'!$A:$A, 0))

This formula can be dragged across the columns and the B:B reference will
increment. B is the returned value.

 

[Dave Peterson]

That should have worked.

But I think it does too much work as will slow down your workbook when it
recalculates.

Instead I'd dedicate as separate column that would return the number the
matching row:

Say in column B (cell b6???)
=match(a6,'[datasheet.xls]sheet1'!a:a,0)
Then drag this portion down the column as far as you need it.

Then you can use:
=index('[datasheet.xls]sheet1'!b:b,b6)
and
=index('[datasheet.xls]sheet1'!c:c,b6)
....

You may want to make sure that you did find a match.

=if(iserror(b6),"no match",index('[datasheet.xls]sheet1'!b:b,b6))
....

So the important part of your formula (the portion that looks for a match) is
only calculated one time.

I have a spreadsheet with about 50 columns and up to several hundred rows
with column A containing the Team Name.
I have a simple table setup with each of the team names on one tab of a
sheet...on the 2nd tab, I was trying to run a v-lookup to return up to the
first 50 of each team name along with the data from first 22 columns....

The formula is/was
=vlookup(TeamTableA!6,'[DataSheet.xls]Sheet1'!$A:$V,{1,2,3,etc up to
22},FALSE)

I figured the array {1,2,3} would return the first 22 columns in the
row....but apparently not..

I also only want to return up to the first 50 instances of each team name.
I can figure out how to make each unique if need be but any help on getting
the lookup to return the first 22 columns in the row would be appreciated.