ISTEXT function question

Asked By todd adams
07-Nov-09 12:51 AM
Earn up to 0 extra points for answering this tough question.
I want to write a formula that will give me a number value when any number of text sequences are discovered in another cell. The spreadsheet is for a football pool so I want it to calculate based on the team and value they risked. So, it should look like this;

C1- 16
C2 - Team in text
C3 - if team in text is any one of a list of winners then give value of C1

I hope I explained this well enough because I know nothing about formulas.

Thanks in advance

  RE: ISTEXT function question

Jonathan VH replied to todd adams
07-Nov-09 06:58 AM

As you write "discovered in another cell," I infer that the "list of winners" is entered in one cell. With that cell  as A1, then try a formula like this in C3:

=IF(ISERR(SEARCH(C2,A1)),"",C1)

If the "list of winners" is actually in a range of cells, with each winning team name in its own cell (which is a more natural way to represent a list in Excel, then use a lookup function, e.g. if the list of winners is in the cells A1 through A10:

=IF(ISNA(MATCH(C2,A1:A10,0)),"",C1)

Create New Account