Using the Find Function

[Phulishone]

So I have 2 worksheets. One has a list of Player Names. The other has a list of Plays that any of these players could have been involved in (or they may not have been involved in).

I am trying to use the find function to find anyone of those player names from sheet 1 in each of the plays. My functions currently stands at

=FIND(('Dinas Roster'!$A$2:$A$21),I6,1)

Where Dinas Roster is the list of players and I6 is a particular play and would go to I7, I8....which are the plays,

This isn't working as I am getting all #Value. If I just do it for 1 Player (i.e. Dinas Roster'!$A$2) it works and returns a number on each line that player shows up in...


any advice

[JBeaucaire]

Your trying to use a single instance function in an array without specifying what to do with the array results.

Post an example of exactly what you'd like to see with this formula, the RESULT. We'll look at that and determine the formula.

[Phulishone]

Thanks for the response. I will do my best.

So on worksheet 1 I have the following list of name and positions:

Name.................. Position
Chrisarlie Wardeinke .... Offense
C Dub ................Offense
Bobblehead Jones....... Offense
Iam Legend.......... Offense
Ham Bone........... Defense
Urbansa Enema.... Defense
Jerzy Jetzzz....... Defense
Jay Longkick....... Special Teams
Jack Swift.......... Special Teams


On the 2nd sheet I have a list of plays:

Iam Legend rush to the right (4 yd gain) [tackle: Reef McBeefmeat]
C Dub pass to Mr Turtle, hurried by Maximus Brutus, over the middle (13.5 yd gain) [diving tackle: Boris Johnson]
Iam Legend rush up the middle (3.5 yd gain) [tackle: The Tumbledryer]
Iam Legend rush to the right (2 yd gain) [tackle: Reef McBeefmeat]
C Dub sacked by Boris Johnson (5 yd loss)
C Dub pass to Class Act over the middle, PAT made by Jay Longkick [TD] (4.5 yd gain)
Punt by Jack Swift, 46 yd, fielded by Dedrick Dodge (10.5 yd return) [tackle: Foolin' Pascagoulan]



To the right of the Play I want it to tell me if it was a player on offense or defense. So for example

Punt by Jack Swift, 46 yd, fielded by Dedrick Dodge (10.5 yd return) [tackle: Foolin' Pascagoulan] Special Teams
Iam Legend rush to the right (2 yd gain) [tackle: Reef McBeefmeat] Offense

I would want it to use the 1st one it comes to as well if possible?? For example

C Dub pass to Class Act over the middle, PAT made by Jay Longkick [TD] (4.5 yd gain) Offense (even though it would also find Jay Longkick)

If I had to I could just have it use a table of offensive players and if it didn'tfind them default Defense/Special Teams or something

Any thoughts?

[JBeaucaire]

I thought "post a sample" would get us a workbook to look at.

[Phulishone]

sorry!! Tab 1 you will see my failed attempt at the formula

[JBeaucaire]

Create another column to the right, and manually enter 5 or 6 examples of what you want the desired result to be, and how you figured that. Manually enter it, no formulas, just show us.

[Phulishone]

Create another column to the right, and manually enter 5 or 6 examples of what you want the desired result to be, and how you figured that. Manually enter it, no formulas, just show us.

ok, I think I gave you what you need.

[JBeaucaire]

I still think there's thoughts in your mind that aren't on the page yet. Sorry. So, in every single example you gave, we're looking for Iam Legend? How is that determined?

Is Excel supposed to look in a the Description field and figure out what name to use and where it is in the field all by itself? Or are you putting Iam Legend somewhere to indicate to those fields that is the name that is supposed to be found?

If Excel is supposed to find the first name in the Description field, were all those Iam Legend examples wrong? Is the second one supposed to be C Dub? And I11 supposed to be Jack Swift?

If Excel is supposed to find the first name listed, can we get you to always start the description with the name? Instead of "Punt by Jack Swift" you state it as "Jack Swift punts"...

[JBeaucaire]

Here is a first blush based on the idea you wanted the FIRST NAME found in the DESC column to be your determining factor. This required you to sort your list on Dinas Roster. There's a Sort Roster button added for convenience.

Now, as long as the first two words in the DESC are a name, and the name is in the list, you will get an answer. If the name is missing from the roster, "Name not found" will appear.

Also, a note about your Dinas Roster list. All the names in that roster have a trailing space after it. It's invisible, but it breaks the formulas on Sheet4 if I didn't account for it, so I did. If you decide later to fix your roster so it doesn't have that invisible space after every name in the list, let me know and I'll fix the formula for you. You just need to remove the &" " references.

Have a look.

[Phulishone]

Here is a first blush based on the idea you wanted the FIRST NAME found in the DESC column to be your determining factor. This required you to sort your list on Dinas Roster. There's a Sort Roster button added for convenience.

Now, as long as the first two words in the DESC are a name, and the name is in the list, you will get an answer. If the name is missing from the roster, "Name not found" will appear.

Also, a note about your Dinas Roster list. All the names in that roster have a trailing space after it. It's invisible, but it breaks the formulas on Sheet4 if I didn't account for it, so I did. If you decide later to fix your roster so it doesn't have that invisible space after every name in the list, let me know and I'll fix the formula for you. You just need to remove the &" " references.

Have a look.

Thanks JB! So I see you added some names to the Roster, actually I do not want excel to find those names (I want an error to occur as those are plays I do not want to focus on).

To answer your questions above:


I want excel to look at all the names in the Roster and if it finds any of those names in the description I want it to return something. Ideally I want it to return what is to the Right of the name in the roster (i.e. Offensive), but if it just returns True or a number I can work with that. I have reattached the spreadsheet with what I hopeis further clarification.

Be sure to note also the example I have highlighted further down the page...

My apologies for making this so confusing!

[JBeaucaire]

You aren't saying this clearly, so let me say it if this is true:

You want a formula that can find SEVERAL names in a string of descriptive events, then give you a lookup answer (Offense/Defense/Sp.Team) on any of the names it find in the description that are on your roster. So, it's not a positional thing, a formula that can find names anywhere in the string, several of them, in fact.

Is that right? If so, good luck with that. You have a major redesign of your sheet ahead of you to accomplish that. Or someone willing to spend a LOT of time writing VBA code.
==========
The Dinas-2 sheet is my best offer. The ability to lookup the first two words as a name and give you that person's position.

To find several names, your Description field will need to become several fields, one for each person's part of the play.

I6 = C Dub passes to
J6 = Mr Turtle (over the middle),
K6 = Maximus Brutus hurries the pass
L6 = (13 yd gain)
M6 = [diving tackle: Boris Johnson]

Then a formula in N6 could possible display all the pieces like you see it now:

N6 = I6&J6&K6&L&M6

The benefit of this method is you COULD use the LOOKUP technique I showed you to get the first two names in a cell and compare that to your list, you could then create a version that checks its way through that string of cells to determine the name in each one and if it's on your list, eventually getting a match and give you your result.

I couldn't design anything that intricate in a single cell.

[Phulishone]

Hey JB, I found a colleague at work who could help, This is what they came up with an it worked!!

=INDEX('Dinas Roster'!$H$1:$H$45,MATCH(TRUE,ISNUMBER(SEARCH('Dinas Roster'!$A$1:$A$45,I9)),0))

I had to enter it with CTRL+Shift+Enter


Now I just need them to explain it to me!!

Thanks for looking at it though.

[JBeaucaire]

Ok, glad you're happy. But it doesn't do what you've outlined to me, though it does seem to work for the first couple of names, which is more than I could've done.

I13 has two failed names, followed by two names that should generate a match, but your new formula doesn't match the 2nd or 3rd name.