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.

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.

--
HTH...

Jim Thomlinson


"PeteT." wrote:

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.

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.

PeteT. wrote:

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.

--

Dave Peterson