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" 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 errorNext: Keyboard shortcut for Paste Special-Values