Searching based on text

[EJ Hill]

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?

 

[ozgrid.com]

Try a VLOOKUP or INDEX/MATCH combo.

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

 

[Dave Peterson]

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]

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.

 

[EJ Hill]

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...

 

[Dave Peterson]

I'd say there was a mistake in either the formula I posted and you modified --
or a mistake in the way you modified the suggested formula.

I think it's time to post the formula you used.

 

[EJ Hill]

=vlookup(B4,if(b4="QB",'Roster!'N4:AM110league!,'Roster'!17,False))
if(b4="K",Roster!N4:AM110league!,'Roster'!25,False)

I modified the sheet so that the stats all report to the end of the "Roster"
worksheet.
But this says it doesn't like the pathing name 'Roster'

 

[Dave Peterson]

You're going to end up with the =indirect() version. You have too many criteria
for the =if() statement to handle.

(Unless you're using xl2007, you can't nest those 11 conditions in the =if()
portion of the formula.)

And I'm confused about what you want to lookup.

I thought B5 was going to contain the name of the player and B4 would contain
his position.

If that's true, then you're going to have to use something like:

=vlookup(b5,if(b4="QB",'Roster'!N:AM,
if(b4="K", 'Roster'!??:??,
if(b4="CB",'Roster'!??:??,
'Roster'!??:??))),25,false)

The =if() in the middle is gonna evaluate to a range on that roster worksheet.
You'll have to change the ??:?? to the correct columns.

Again, I think that you're gonna find that this is a non-starter.

You're next attempt should be the worksheet with the table of positions and
columns to use.