Prev: vlookup formula error
Next: Keyboard shortcut for Paste Special-Values
From: EJ Hill EJ on 9 Apr 2010 23:24
I am building an Excel to recall stats for TV sports. There are three
workbooks, Home, Away and League. The league sheet has all of last season's
stats.
In all workbooks the player's name is in column A.

Because each player has different stats based on his position, I want to
select the columns returned based on a text string.

Example: In cell A4 of the home sheet is the name JOE SMITH. In A4 is the
text CB.
In cell B5 is the name JOHN DOE and in B4 the text QB.
For the CB it needs to return stats from column G, H, J and N based on the
player's name. For the QB it needs H, I, J, K, L and M.

Is this possible?
From: ozgrid.com on 9 Apr 2010 23:53
Try a VLOOKUP or INDEX/MATCH combo.

http://www.ozgrid.com/Excel/excel-vlookup-formula.htm
http://www.ozgrid.com/Excel/left-lookup.htm



--
Regards
Dave Hawley
www.ozgrid.com
"EJ Hill" <EJ Hill(a)discussions.microsoft.com> wrote in message
news:0C839D0A-674F-4300-A4CD-71266AA2E138(a)microsoft.com...
>I am building an Excel to recall stats for TV sports. There are three
> workbooks, Home, Away and League. The league sheet has all of last
> season's
> stats.
> In all workbooks the player's name is in column A.
>
> Because each player has different stats based on his position, I want to
> select the columns returned based on a text string.
>
> Example: In cell A4 of the home sheet is the name JOE SMITH. In A4 is the
> text CB.
> In cell B5 is the name JOHN DOE and in B4 the text QB.
> For the CB it needs to return stats from column G, H, J and N based on the
> player's name. For the QB it needs H, I, J, K, L and M.
>
> Is this possible?

From: Dave Peterson on 10 Apr 2010 08:59
I think you have a typo in your example (for row 4).

This may get you closer:

=vlookup(B5,if(b4="QB",league!H:M,if(b4="CB",league!G:N,lots more here),2,false)

That if statement in the =vlookup() just points at the other ranges.

If the formula gets too complex, you could create a new sheet with a table on
it:

ColA Cols
QB H:M
CB G:N
....(lots more)

Then you'd have to use =indirect() to change that string to a real range
reference.

=vlookup(b5,indirect("'league'!"&vlookup(b4,Table!a:b,2,false)),2,false)

=indirect() is a volatile function. It'll recalc whenever excel recalculates.
So if you use lots of them, it may slow down calculations. But it makes the
formula easier to use (I think).

And when I'm doing this kind of stuff, I'm retrieving the values and as soon as
I get it right, I convert the formulas to values.

You may want to consider that, too.

EJ Hill wrote:
>
> I am building an Excel to recall stats for TV sports. There are three
> workbooks, Home, Away and League. The league sheet has all of last season's
> stats.
> In all workbooks the player's name is in column A.
>
> Because each player has different stats based on his position, I want to
> select the columns returned based on a text string.
>
> Example: In cell A4 of the home sheet is the name JOE SMITH. In A4 is the
> text CB.
> In cell B5 is the name JOHN DOE and in B4 the text QB.
> For the CB it needs to return stats from column G, H, J and N based on the
> player's name. For the QB it needs H, I, J, K, L and M.
>
> Is this possible?

--

Dave Peterson
From: EJ Hill on 10 Apr 2010 10:09
Yes, there's a typo. I shouldn't post when I'm exhausted.

I'll try your suggestions. The formulas are going to get complex because
there are 11 different player positions that need to sorted. The good news is
that football teams only play once a week so after the formula's run once,
I'm golden.

I can't do much with the way the data is entered as it is done on a web
query. In the past I manually entered the all the data into the graphics
machine on game day.

From: EJ Hill on 10 Apr 2010 11:05
Dave -

Tried your suggestions. I kept getting a "too many arguments" error. Could I
be searching through too many records? There are almost 1500 players in that
registered stats in the league last season...
 |  Next  |  Last
Pages: 1 2
Prev: vlookup formula error
Next: Keyboard shortcut for Paste Special-Values