How to check if a column contains a certain word?

**The_Snook** · 02-22-2013, 05:27 AM

Hi,

I have a transaction list of invoices and column N contains their status (processed/unprocessed/query). How can I create a formula to check whether any of the invoices are still in query, i.e. it looks in column N for the word 'query' and returns 'yes' if any are and 'no' if any aren't.

Thanks,

Snook

**Fotis1991** · 02-22-2013, 05:35 AM

Something like this?

=IF(N2="QUERY","YES",NO)

OR

=if(countif(n:n,"query")>0,"yes","no")

**RHCPgergo** · 02-22-2013, 05:39 AM

Hi Snook,

something like this?

Formula:

Please Login or Register  to view this content.

edit: I swear I didn't copy you Fotis... :D

**The_Snook** · 02-22-2013, 06:06 AM

That work a treat, thanks! How could I amend the formula to look at the columns in each monthly tab using the indirect function and the data in row 2? Example screenshot attached.

INDIRECT Query.jpg

**RHCPgergo** · 02-22-2013, 06:44 AM

This should work:

Formula:

Please Login or Register  to view this content.

Copy it in C3 and drag right.

**RHCPgergo** · 02-22-2013, 06:45 AM

double post

**The_Snook** · 02-22-2013, 08:43 AM

Sweet as a nut RHCPgergo! Massive thanks to both of you for your help today! The swift assistance I receive as part of this community really does make my working life so much easier. I'm gradually learning more and more about Excel and I look forward to the day I can start to give something back and assist others.

Snook