If then and scanning for specifc text with index and match ?

[Ron Purpura]

Hello All
A 2 part question, am getting hung up with if then and match and scanning for a text in a cell.

I would like to match the # in Column A with the # in Column H , and if there is a YES anywhere in column O that corresponds to the # in Column A then I would like to return a YES in column C. If there is a NO in column O then I would like to return a NO

EXAMPLES COLORED IN ORANGE


Secondly I would like to do the following

I would like to match the # in Column A with the # in Column E, and scan the text in corresponding sell in Column F for the word "*delirium". If that word resides in that cell, I would like to return a YES to column B, if not, then return a NO

Examples colored and highlighted in RED


Many thanks in advance

[Limor_OP]

Hi,
column O is empty...
which column did you mean?

[Ron Purpura]

So sorry, I meant column N

Thank you Belinda200

[Limor_OP]

Thanks.....!

[Limor_OP]

Deleted-----

[Limor_OP]

Try this -

B2 and down:

=IFNA(IF(SEARCH("delirium",INDEX($F$2:$F$21,MATCH(A2,$E$2:$E$21,0)))>0,"YES",""),"")

C2 and down:
=IFNA(IF(MATCH(A2,($H$2:$H$21)*($N$2:$N$21="yes"),0)>0,"YES",""),"")

[Limor_OP]

I'm also attaching the file.
Let me know if this was what you meant....

[Ron Purpura]

Belinda200
Thanks so much. Is there a way I can return a "NO" in column C instead of a blank cell ? For some reason when I try to substitute a NO for the blank, it makes the formula invalid
Thank you again ! This is wonderful

[Limor_OP]

Hi
Sure,
Try this in C2 :
=IFNA(IF(MATCH(A2,($H$2:$H$21)*($N$2:$N$21="yes"),0)>0,"YES",""),"NO")

[Ron Purpura]

I am so sorry to be a bother Belinda200. For some reason when I use that formula , it returns a "NO" in every cell. That does not make sense to me ..Any ideas ?

[Limor_OP]

Cells 5,7, and 16 show "YES"

[Ron Purpura]

Thank you Belinda200. I must be doing something wrong, I greatly appreciate your help

[Limor_OP]

You're welcome : )