Modify Index with Match Formula

[Rob]

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

 

[Bernard Liengme]

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

 

[T. Valko]

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")