Search rows for words and return "Yes"

[Sparkyc]

Hi all,

Im new around here. I tried to look if this has been asked/answered before but couldn’t find anything.

I need to do a search of a spreadsheet and see if any row has “white” in column B and “priority” in column I, return value yes in a different cell.
I have tried a few ways and can get it to do either search individually and search column B and I for them words but not if they are in the same row.

Any help would be greatly appreciated.

Thanks

[jeffreybrown]

Give this a try

=IF(AND(B1="white",I1="priority"),"Yes","No")

[Sparkyc]

Thanks for the speedy reply
That works, however the spreadsheet is populated daily and averages 300 lines a day is there a way of doing this for every line without specifying b1, b2, b3, etc?

Thanks in advance

[jeffreybrown]

Not readily seeing how that is possible. Excel is looking for TRUE and FALSE, but how are we going to tell the formula to look at a word if it is not in a cell.

Maybe you can explain a little more on what you are trying to achieve and why pointing to a cells or cells will not work for you?

[Sparkyc]

I will try to add a demo of the spread sheet.
The reason it will be difficult to do pointing at cells is because the spreadsheet being 300 rows long (Currently and will be doubling soon) looking at every cell would mean the formula would be huge. for example for 5 lines alone it would be

=IF(AND(B1="white",I1="priority",B2="white",I2="priority",B3="white",I3="priority,B4="white",I4="priority),B5="white",I5="priority)))),"Yes","No")

In the full spreadsheet there is 300 lines and 23 different colours to search for.
In the demo you can see I would like the result in M2 for White, M3 For Blue etc

I hope this makes sense?

[jeffreybrown]

Thank you for your spreadsheet example as that does clear things up. How's this?

[Sparkyc]

You are a god.

[jeffreybrown]

Glad this has helped you out and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.