Find Specified Text in Column, then Find Specified Text in Row Cells - return "yes" or "no"

[atypicalv]

I've tried searching for this, but not having any luck so far.

I am looking for a formula to search for specified text in column A, and if found in column A, it then will search a specified cell in the same row for whether it has the text "OFF" or not. If it does have "OFF", it will return a "NO", if it doesn't have "OFF", if would return a "YES".

Appreciate any assistance.

 

[atypicalv]

Also, is there a reason anyone can see why this formula isn't searching the range? {=IF(A64=A4:A55,IF("OFF"=B4:B55,"NO","YES"),"NO")}

 

[Rick Rothstein]

Also, is there a reason anyone can see why this formula isn't searching the range? {=IF(A64=A4:A55,IF("OFF"=B4:B55,"NO","YES"),"NO")}

Just looking at the part I colored... the single cell A64 can never be equal to all the cells in A4:A55.

 

[Rick Rothstein]

Using the first (2003): {=IF(ISNUMBER(MATCH(A64,A4:A55,0)),IF(COUNTIF(INDIRECT(MATCH(A64,A4:A55,0)&":"&MATCH("OFF",F4:F55,0)),"OFF"),"YES","NO"),"")}

(Note: A64 is where the search text is on this sheet, using the range A4:A55 in the match "OFF" function returned a #N/A, so I changed it to search the range desired - F4:F55)
Results:

1. 
   [*=1]If the text in A64 is found in A43, and the "OFF" text is found in F43, it is returning a "YES" (desired is a "NO").
   [*=1]If the text in A64 is NOT found in the range, the return is empty (nothing in the cell)
   [*=1]If the text in A64 is found in A43, and the "OFF" text is NOT found in F43, it is returning a "YES" (this is the desired function)
   [*=1]So always a "YES" returned, unless the first search criteria is not found in the A range, then it returns an empty cell.

Using the second: {=IFERROR(IF(COUNTIF(INDIRECT(MATCH(A64,A4:A55,0)&":"&MATCH("OFF",F4:F55,0)),"OFF"),"YES","NO"),"")}

(Note: the second search range (looking for "OFF") changed to be F4:F55 from A4:A55)

I am confused... why are you searching Column F for the word "OFF"... is the word "OFF" always going to be in Column F? If you are having trouble making my formula work, then I think you are going to have to upload a copy of your workbook so that we can see what your real data is. You can post it to one of free file-sharing websites on the Internet, for example, www.box.net/files is one of them. Whichever one you use, make sure you post the URL that the website gives you for your file back here in this forum.

 

[atypicalv]

This is for preparing a work schedule. Column "A" has job types, Columns B and over represent days of the week-the cells in these columns have employee names, or "OFF" if no one is scheduled for that job type on that day. I would like to be able to see if we have a specific job type scheduled on a specific day. So if the job type searched for in column "A" is found, it then looks in the specified day column to find if it has "OFF", it it does have "OFF" it returns a "NO" (meaning no one is scheduled). If it doesn't find an "OFF" it returns a "YES", meaning someone is scheduled. So "OFF" will only sometimes be in the columns.

I've uploaded the workbook to https://www.box.com/s/jl5k2zunxs1sm610prtz . The "Data" sheet is the one I'm working with: cells A64:A83 is the example of job types we'd search for.

Lastly, thank you for the explanation on why this formula isn't searching the range: {=IF(A64=A4:A55,IF("OFF"=B4:B55,"NO","YES"),"NO")} , that makes sense and I'm learning.

Thanks for the help, cheers.

 

[Rick Rothstein]

I sure am glad you ended up posting your file so that we could download it... without see it, we could have gone back-and-forth for weeks without with nothing ever working... the layout you have is non-standard and not one anybody would have ever been able to figure out... you have merged cells, each item makes use of two rows and there are blank rows between each double-row item. I'm not sure how easy it will be to give you a formula solution... we might have to resort to a VBA macro, not sure yet... but we need a clarification on those repeated "L Salad" entries. Unless you number them "L Salad", "L Salad 2", "L Salad 3", etc. like you number, for instance, "D Salad" and "D Salad 2", there can never be a way to distinguish on "L Salad" from the next "L Salad", so the natural question is... why aren't they numbered and, more importantly, can they be numbered?

 

[atypicalv]

The "L Salad" job type won't be more than once in the "A" column, apologies for the confusion on that. As the sheet is a prototype and has been used for testing, multiple cells have been toggled to "L Salad", but that wouldn't be a real world scenario.

Thanks for the help with this, it's certainly beyond my excel expertise.

 

[Rick Rothstein]

The "L Salad" job type won't be more than once in the "A" column, apologies for the confusion on that. As the sheet is a prototype and has been used for testing, multiple cells have been toggled to "L Salad", but that wouldn't be a real world scenario.

Actually, the things I was worried about appear not to be the problem I was anticipating they would be. Give this a try - put this formula in B64 and copy it down...

=IFERROR(IF(COUNTIF(INDIRECT(MATCH(A64,A$1:A$55,0)&":"&MATCH(A64,A$1:A$55,0)),"OFF"),"NO","YES"),"")

 

[atypicalv]

For what it's worth, this formula does work: {=IF(COUNTIF(A4:A55,A64)>0,IF(COUNTIF(B4:B55,"OFF")>0,"NO","YES"),"NO")}

However, it stops working if you do the following specific sequence: with the search criteria in column "A" of "L Saute", say "A40", and something other than "OFF" in "B40", the result is "YES" (correct). If you change "B40" to "OFF" - the result is "NO" (correct), and then change "A40" to something other than "L Saute", it still returns "OFF" (correct). But now if you change "A25" to "L Saute", and "B25" is not populated with "OFF", the result is "NO" (incorrect, should be "YES"). From that point on, the function no longer works, no matter which cells are changed.

If you don't do that specific sequence, the formula works up and down the range correctly: you can toggle "A" cells to different values without breaking the function, and whenever you have "L Saute" in a cell, it will give the appropriate return of "NO" if the corresponding "B" column cell has "OFF", or a "YES" if the "B" column cell doesn't have "OFF".

Again, beyond my ability with excel to answer why it's breaking...

 

[Rick Rothstein]

For what it's worth, this formula does work: {=IF(COUNTIF(A4:A55,A64)>0,IF(COUNTIF(B4:B55,"OFF")>0,"NO","YES"),"NO")}

Whether you get that formula working or not, it is only looking in Column B for the word "OFF", so if "OFF" is in some other column, it won't see it. Did you try the formula I posted in Message #17 yet? By the way, I forgot to mention... the formula I posted in Message #17 is a normally entered formula... it is not and array formula.

 

[atypicalv]

Actually, the things I was worried about appear not to be the problem I was anticipating they would be. Give this a try - put this formula in B64 and copy it down...

=IFERROR(IF(COUNTIF(INDIRECT(MATCH(A64,A$1:A$55,0)&":"&MATCH(A64,A$1:A$55,0)),"OFF"),"NO","YES"),"")

Thanks Rick.

OK, I put this in {=IFERROR(IF(COUNTIF(INDIRECT(MATCH(A64,A$1:A$55,0)&":"&MATCH(A64,A$1:A$55,0)),"OFF"),"NO","YES"),"")} . I'm getting "NO" when a cell in "A" matches the search criteria and the corresponding "B" cell is either "OFF" or "Rachel", if the search criteria is not found in "A" at all, the return is an empty cell (blank). With the search criteria showing in the "A" cell, changing the corresponding "B" cell has no effect, i.e. with "OFF" the return is "NO", with "Rachel" the return is still "NO".

Can't seem to get it do anything else.

Edit:
We are posting "over" each other , I'll try without the array. Also, for the aforementioned formula: it would be OK if it only looks in one column at a time for "OFF", the intention is to have the search criteria for each day, so each day would have its own formula for the job types. i.e. Monday would be {=IF(COUNTIF(A4:A55,A64)>0,IF(COUNTIF(B4:B55,"OFF")>0,"NO","YES"),"NO")}, Tuesday would be {=IF(COUNTIF(A4:A55,A64)>0,IF(COUNTIF(F4:F55,"OFF")>0,"NO","YES"),"NO")} , and so on. I hope that makes sense...

Edit #2:
OK, tried without the array, no change.