#### Searching based on text

```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?
```
 0
Utf
4/10/2010 3:24:01 AM
excel.worksheet.functions 4936 articles. 2 followers.

7 Replies
774 Views

Similar Articles

[PageSpeed] 23

```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@discussions.microsoft.com> wrote in message
news:0C839D0A-674F-4300-A4CD-71266AA2E138@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?

```
 0
ozgrid
4/10/2010 3:53:18 AM
```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
```
 0
Dave
4/10/2010 12:59:25 PM
```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.

```
 0
Utf
4/10/2010 2:09:01 PM
```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...
```
 0
Utf
4/10/2010 3:05:01 PM
```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 wrote:
>
> 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
```
 0
Dave
4/10/2010 8:42:45 PM
```=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'
>
```
 0
Utf
4/10/2010 11:53:01 PM
```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.

EJ Hill wrote:
>
> =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
```
 0
Dave
4/11/2010 12:49:58 AM

Similar Artilces:

Shrinking Text
Hi everybody, in our Office (Word) 2007 application, WTS 2008x64, the following happens (for some - not all- users): A text is written and formatted normally and without any problems. The document shall be printed. After the printjob is executed, the chracters of the text lines are compressed to only a few millimeters in width. The font height remains normal. Nothing is legible. This now ocurs on the printed doc as well as on the screen. After the doc is saved and opened in a different session, everything is OK and works well... What can I do? As always, every help and ...

Finding characters within a text
Hi How to check, using single formula, that a text within a certain cell contains one of certain characters? For instance, how to check if there is a 'R', 'L' or 'Ps' character within cell A1 that reads 'W-RII'. Thanks in advance Hmm..not very elegant, but maybe =NOT(AND(ISERROR(FIND("R",A1)),ISERROR(FIND("L",A1)),ISERROR(FIND("P",A1)))) Returns TRUE if the text in A1 contains 'R', 'L' or 'Ps' . FIND is case-sensitive, use SEARCH if you also want to find lower-case characters. Cheers, Joerg Mochiku...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

What is 'Align Text to Base Line Guide'?
Trying to make sure all lines have the same amount of space between them I selected paragraph from the format menu and under the line adjustments there is a box 'align text to base line guides' Ok I did that, now there are lines at the margines of my newsletter. What are these? Also selecting this pushed my text down from the top edge of the text box. What is this? The baseline guide measurements are in the Arrange, Layout Guides, Baseline tab. There is help in the Help menu. -- Mary Sauer http://msauer.mvps.org/ "Go_Girl3647" <GoGirl3647@discussions.microsoft.com...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Text Effects Scrolling or Blinking text
How do apply the scroll or blinking feature to text that you want to emphasize in a word document. Prior to MS2007 there was a feature on the fonts tab that allowed a user to apply the following features to text "marching ants, blinking text, scrolling marquee, etc. I cannot find the any of these features anywhere in Word 2007. "HELP" There is no direct access to them, but they are still available by macro I posted the following macro recently, for use in Word 2007 Sub AnimateFont() Dim sAnimation As String If Len(Selection.Range) = 0 Then MsgBox "...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

How do text capture tools work?
Here are two examples of this technology. http://www.textcapture.com/en/default.html http://www.deskperience.com/textcapture/ Sounds like they will have to involve OCR, which works fine for standard fonts but probably won't work with fancy fonts. Key here is to test them try a static control with a TextOut and change the fonts and see what happens. joe On Wed, 31 Oct 2007 15:02:04 -0500, "Peter Olcott" <NoSpam@SeeScreen.com> wrote: >Here are two examples of this technology. > > http://www.textcapture.com/en/default.html > http://www.deskp...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

Spiral text
Does anyone know how to create spiral text? In a draw program for sure. You probably can get close in Publisher, but it will not look as good as a draw program can do. Serif has a free program you can try. DrawPlus http://www.freeserifsoftware.com/ -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "jaykay" <jaykay@discussions.microsoft.com> wrote in message news:71954A65-C46F-4005-9553-B324EFEE7500@microsoft.com... > Does anyone know how to create spiral text? I've downloaded the program, but can't figur...

Email Text Dissappears when Save
When we save email messages (as a draft) the text dissappears, even when th I use my administrator account. Anybody have any idea what that is happening. ...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

ComboBox with AutoCompleteMode = SuggestAppend and custom search (Contains)?
Hi, I have a class FlightStation with a Search property that combines two attributes. I like to search not only for matching strings from the beginning, but rather from any where in the string. (With the Contains method or the string class) What is the best and quickest solution to implement such a search when using a combobox and not a TextBox for the input? I know about implementing a derived class like this: class FlightStationBindingList : BindingList<FlightStation> { // ... } But is there a more quick and dirty solution ?! - Sorry :-) My solution with the Tex...

Enable/Disable a Form Control Based on Security Group Permissions
How do I enable or disable a control in a form based on a user’s security group membership? For example: If I have a checkbox on a form (call it box1), I want box1 to be enabled if the user who opened Access is a member of a security group called “Breaker Test Admin.” For members of any other group (except of course “Admins”), box1 should be disabled. Thank you, for your help! On Mon, 02 Jul 2007 18:57:13 GMT, "BenS" <u35527@uwe> wrote: >How do I enable or disable a control in a form based on a user�s security >group membership? For example: If I have a checkbox on a ...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

Setting Text in a ComboBox
Hi, Does anybody know why I cannot set the text in the edit control of a CBS_DROPDOWN style CombBox control using SetWindowText()? I was able to change the text in the edit control part of the ComboBox manually. Thanks, Vincent. I don't know why can't you? Post some code. You should be able to m_MyCombo.SetWindowText("The Text"); Ali R. "Vincent Yu" <anonymous@discussions.microsoft.com> wrote in message news:5e2b01c3ad48\$5309d630\$a601280a@phx.gbl... > Hi, Does anybody know why I cannot set the text in the > edit control of a CBS_DROPDOWN style ...

anchor picture to text box
(Publisher 2003) I have a newsletter with a Kudos box. In the corners of the box I have a happy face. I'd like the happy faces to be anchored to their corners so when I enlarge or shorten the box, the faces move along with the text box. Why not create a rectangle, insert the happy faces, group. The problem as I see it would be if you resize, the happy faces could be distorted if you widen or shrink the group. You would always have to re-size proportionally. -- Mary Sauer http://msauer.mvps.org/ "Helen" <Helen@discussions.microsoft.com> wrote in message news:193...

Text Ticker
Hello Experts i was asked to do a simple project 1-want do a text ticker can be scroll left to right and right to left 2-transparent 3-no flicker 4-has a background image please where can i start and how to do it thank you BitBlt background to offscreen bitmap TextOut to the bitmap with appropriate offset and clipping region BitBlt offscreen bitmap to client area of screen Use a subclassed CStatic joe On 18 Aug 2006 20:59:22 -0700, "Prafulla T" <prafulla.tekawade@gmail.com> wrote: >Hello Experts > >i was asked to do a simple project >1-want do a text tic...

text box email publication
I am a publisher novice. I am trying to develop an e-mail newletter. The templates in publisher have a text box under each article with what appears to be a hyperlink with "more details". I assume this will enable the reader of the email to expand the text box and see the full details of the article. I like this feature as it means that readers only get to see the title and abstract and then can select the articles they want to read in full. Unfortunately I can not work out how to use this function and as usual help is no help. Tim no such feature in Publisher. "kiwi ti...

Transform fixed length text to xml
can i transform fixed length text to xml using XSL do i have to first load the text to xml and do the transformation? is there any other way to do it? ...

Finding text within text
Hello again, I've gotten the formula for finding occurrences of a word in one column based on whether it's in another column; now what I need is to find out whether a word in column A (for instance) appears in a string of text in column B. As in, a SKU in A, and a description in B, with the descriptions having several SKUs in each one. I need to be able to delete from a sheet any description that doesn't have the SKUs I'm looking for, and it would be really nice to color the corresponding cells the same, but even having the row number in the third row would be nice. Say I...

Blocking e-mail based on message text.
Hi! I've got a question concerning e-mail blocking. I'm using Exchange 2003 and TrendMicro MailScan for antivirus. Antivirus removes infected attachment, but lets the letter pass through. There's no "remove e-mail function", so the users have been complaining about those particular letters, saying "Your account has been blocked". Is there a way to configure Exchange to delete e-mail based on the subject (e-mail text)? I know that you can block it in outlook, but that's the last resort. Thank you. "Igors Belijs" <IgorsBelijs@discussions.mi...