Help! Array formula for data that uses words instead of numbers
 

I have a Little League schedule with 129 rows that looks like this:

A B C
D E
1 9/8 Braves
Orioles vs Nationals
2 9/9 Pirates Red Sox vs Braves Orioles
3 9/10 Muckdogs vs Orioles Red Sox

I want to enter a formula in Column E that tells Excel to check cells b1:d1
in each row and if any cell contains the name orioles, regardless of whether
it's alone or as part of a match-up, then the text "baseball today" should
appear in the corresponding row in Column E. I'd use this formula in column
E for each row of my spreadsheet. I tried using the formula
=if(ISNUMBER(SEARCH("*orioles*", b1:d1)), "baseball today","") and
Ctrl+Shift+Ent but it doesn't work. If I change the formula so that instead
of an array, it references the specific cell that contains the word orioles,
for example instead of using b1:d1 just using d1, the formula works. Can
anyone help me?

Help! Array formula for data that uses words instead of numbers
 

Try this:

=IF(COUNTIF(B1:D1,"*orioles*"),"baseball today","")

--
Biff
Microsoft Excel MVP


"cecij22" wrote in message
...
I have a Little League schedule with 129 rows that looks like this:

A B C
D E
1 9/8 Braves
Orioles vs Nationals
2 9/9 Pirates Red Sox vs Braves Orioles
3 9/10 Muckdogs vs Orioles Red Sox

I want to enter a formula in Column E that tells Excel to check cells
b1:d1
in each row and if any cell contains the name orioles, regardless of
whether
it's alone or as part of a match-up, then the text "baseball today" should
appear in the corresponding row in Column E. I'd use this formula in
column
E for each row of my spreadsheet. I tried using the formula
=if(ISNUMBER(SEARCH("*orioles*", b1:d1)), "baseball today","") and
Ctrl+Shift+Ent but it doesn't work. If I change the formula so that
instead
of an array, it references the specific cell that contains the word
orioles,
for example instead of using b1:d1 just using d1, the formula works. Can
anyone help me?

Help! Array formula for data that uses words instead of numbers
 

One of the solutions is to concatenate the cells (b1&c1&d1) and search for
the string in that cell...

"cecij22" wrote:

I have a Little League schedule with 129 rows that looks like this:

A B C
D E
1 9/8 Braves
Orioles vs Nationals
2 9/9 Pirates Red Sox vs Braves Orioles
3 9/10 Muckdogs vs Orioles Red Sox

I want to enter a formula in Column E that tells Excel to check cells b1:d1
in each row and if any cell contains the name orioles, regardless of whether
it's alone or as part of a match-up, then the text "baseball today" should
appear in the corresponding row in Column E. I'd use this formula in column
E for each row of my spreadsheet. I tried using the formula
=if(ISNUMBER(SEARCH("*orioles*", b1:d1)), "baseball today","") and
Ctrl+Shift+Ent but it doesn't work. If I change the formula so that instead
of an array, it references the specific cell that contains the word orioles,
for example instead of using b1:d1 just using d1, the formula works. Can
anyone help me?

Help! Array formula for data that uses words instead of number
 

I just started exploring the world of functions in excel...well things that
are more complicated than just summing a range of numbers! Looking at the
formula, it looks way less complicated than what I was making it out to be
lol. Thanks so much. It worked perfectly.

"T. Valko" wrote:

Try this:

=IF(COUNTIF(B1:D1,"*orioles*"),"baseball today","")

--
Biff
Microsoft Excel MVP


"cecij22" wrote in message
...
I have a Little League schedule with 129 rows that looks like this:

A B C
D E
1 9/8 Braves
Orioles vs Nationals
2 9/9 Pirates Red Sox vs Braves Orioles
3 9/10 Muckdogs vs Orioles Red Sox

I want to enter a formula in Column E that tells Excel to check cells
b1:d1
in each row and if any cell contains the name orioles, regardless of
whether
it's alone or as part of a match-up, then the text "baseball today" should
appear in the corresponding row in Column E. I'd use this formula in
column
E for each row of my spreadsheet. I tried using the formula
=if(ISNUMBER(SEARCH("*orioles*", b1:d1)), "baseball today","") and
Ctrl+Shift+Ent but it doesn't work. If I change the formula so that
instead
of an array, it references the specific cell that contains the word
orioles,
for example instead of using b1:d1 just using d1, the formula works. Can
anyone help me?

Help! Array formula for data that uses words instead of number
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"cecij22" wrote in message
...
I just started exploring the world of functions in excel...well things that
are more complicated than just summing a range of numbers! Looking at the
formula, it looks way less complicated than what I was making it out to be
lol. Thanks so much. It worked perfectly.

"T. Valko" wrote:

Try this:

=IF(COUNTIF(B1:D1,"*orioles*"),"baseball today","")

--
Biff
Microsoft Excel MVP


"cecij22" wrote in message
...
I have a Little League schedule with 129 rows that looks like this:

A B C
D E
1 9/8 Braves
Orioles vs Nationals
2 9/9 Pirates Red Sox vs Braves
Orioles
3 9/10 Muckdogs vs Orioles Red
Sox

I want to enter a formula in Column E that tells Excel to check cells
b1:d1
in each row and if any cell contains the name orioles, regardless of
whether
it's alone or as part of a match-up, then the text "baseball today"
should
appear in the corresponding row in Column E. I'd use this formula in
column
E for each row of my spreadsheet. I tried using the formula
=if(ISNUMBER(SEARCH("*orioles*", b1:d1)), "baseball today","") and
Ctrl+Shift+Ent but it doesn't work. If I change the formula so that
instead
of an array, it references the specific cell that contains the word
orioles,
for example instead of using b1:d1 just using d1, the formula works.
Can
anyone help me?