Modify Index with Match Formula
 

I am trying to modify the below formula to look for a single word within a
series of cells instead of matching the entire value and if it finds the word
in a cell then it returns the value of an adjacent cell from where it was
found.

In the example below I am trying to match the word "Maybe" and if it finds
it within a string of text in a cell then it returns the value of what is in
"W"


IF(ISNA(INDEX($W:$W,MATCH("Maybe",$R:$R,0),1)),"No ",INDEX($W:$W,MATCH("Maybe",$R:$R,0),1))


Is this possible or am I S.O.L.?


Thanks In Advance,
Rob

Modify Index with Match Formula
 

You seem to use two contradictory phrases:
a) to look for a single word within a series of cells
b) to match the word "Maybe" and if it finds it within a string of text in a
cell

Are you looking for the word MAYBE as a range of cells that each contain a
single word or a phrase? Can this word be present more than once?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rob" wrote in message
...
I am trying to modify the below formula to look for a single word within a
series of cells instead of matching the entire value and if it finds the
word
in a cell then it returns the value of an adjacent cell from where it was
found.

In the example below I am trying to match the word "Maybe" and if it finds
it within a string of text in a cell then it returns the value of what is
in
"W"


IF(ISNA(INDEX($W:$W,MATCH("Maybe",$R:$R,0),1)),"No ",INDEX($W:$W,MATCH("Maybe",$R:$R,0),1))


Is this possible or am I S.O.L.?


Thanks In Advance,
Rob

Modify Index with Match Formula
 

Try this:

=IF(COUNTIF(R:R,"*maybe*"),INDEX(W:W,MATCH("*maybe *",R:R,0)),"no")

If you use a cell to hold "maybe" :

A1 = maybe

=IF(COUNTIF(R:R,"*"&A1&"*"),INDEX(W:W,MATCH("*"&A1 &"*",R:R,0)),"no")

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
I am trying to modify the below formula to look for a single word within a
series of cells instead of matching the entire value and if it finds the
word
in a cell then it returns the value of an adjacent cell from where it was
found.

In the example below I am trying to match the word "Maybe" and if it finds
it within a string of text in a cell then it returns the value of what is
in
"W"


IF(ISNA(INDEX($W:$W,MATCH("Maybe",$R:$R,0),1)),"No ",INDEX($W:$W,MATCH("Maybe",$R:$R,0),1))


Is this possible or am I S.O.L.?


Thanks In Advance,
Rob