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

atypicalv

New Member
Joined
Mar 11, 2013
Messages
19
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
This sounds like an Index/Match, or a Vlookup.
What's the catch? Is there something more complex going on?
 
Upvote 0
This sounds like an Index/Match, or a Vlookup.
What's the catch? Is there something more complex going on?

From my novice user level, there may not be a catch. :confused: But from what I can tell, vlookup or index/match returns the value of a specific cell if the conditions are met. I am needing to return a "yes" or a "no" based on what it finds in 2 distinct cells. It seems like a IF AND function would do it, but I can't get it to work. e.g. If it finds the word "match" in A20, and finds the word "OFF" in F20, it would return a "NO". If it finds any other word than "OFF" in F20, it would return a "YES".

Thanks for the help.
 
Upvote 0
I'm not sure I completely understand as yet, however here's something that might work for you:


ABCDEFGHIJ
19 search term1search term2result
20Is there a match here? The switch is off matchoffNO

<colgroup><col style="width: 30px;"><col style="width: 158px;"><col style="width: 15px;"><col style="width: 15px;"><col style="width: 15px;"><col style="width: 15px;"><col style="width: 108px;"><col style="width: 64px;"><col style="width: 87px;"><col style="width: 87px;"><col style="width: 59px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
J20=IF(OR(ISERR(SEARCH(H20,A20,1)),ISERR(SEARCH(I20,F20,1))),"YES","NO")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
I think this does what you want, but I cannot believe there isn't a simpler formula...

Excel 2003
----------------------------
=IF(ISNUMBER(MATCH("Specific Text",A1:A21,0)),IF(COUNTIF(INDIRECT(MATCH("Specific Text",A1:A21,0)&":"&MATCH("Specific Text",A1:A21,0)),"OFF"),"YES","NO"),"")

Excel 2007 and above
----------------------------
=IFERROR(IF(COUNTIF(INDIRECT(MATCH("Specific Text",A1:A21,0)&":"&MATCH("Specific Text",A1:A21,0)),"OFF"),"YES","NO"),"")
 
Upvote 0
I think this does what you want, but I cannot believe there isn't a simpler formula...

Excel 2003
----------------------------
=IF(ISNUMBER(MATCH("Specific Text",A1:A21,0)),IF(COUNTIF(INDIRECT(MATCH("Specific Text",A1:A21,0)&":"&MATCH("Specific Text",A1:A21,0)),"OFF"),"YES","NO"),"")

Excel 2007 and above
----------------------------
=IFERROR(IF(COUNTIF(INDIRECT(MATCH("Specific Text",A1:A21,0)&":"&MATCH("Specific Text",A1:A21,0)),"OFF"),"YES","NO"),"")

I'm not sure I completely understand as yet, however here's something that might work for you:


ABCDEFGHIJ
19search term1search term2result
20Is there a match here?The switch is offmatchoffNO

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
J20=IF(OR(ISERR(SEARCH(H20,A20,1)),ISERR(SEARCH(I20,F20,1))),"YES","NO")

<tbody>
</tbody>

<tbody>
</tbody>


Thanks for the replies and assistance, unfortunately I'm not getting either of these formulas to work. Here is a formula that is working, BUT it does not search the range, only the first cells in the range (I need it to search the range):

{=IF(A60=A4:A55,IF("OFF"=F4:F55,"NO","YES"),"NO")} - (Note: I'm using ctrl+shift+enter for the array)

What I need is for it to search for the specified text within the range of A4:A55, if found in "A60" for example, it then checks for the text "OFF" in "F60" (within the range of F4:F55). If it finds "OFF", it returns a "NO", if it doesn't find "OFF", it returns a "YES".

Any ideas?

Thanks for the help!
 
Upvote 0
What I need is for it to search for the specified text within the range of A4:A55, if found in "A60" for example....
Sorry, I am not following this at all... A60 is not within the range A4:A55... why is the code looking in A60?
 
Upvote 0
Sorry, I am not following this at all... A60 is not within the range A4:A55... why is the code looking in A60?


My apologies Rick, I wrote that incorrectly.

It should be "What I need is for it to search for the specified text (located in A60) within the range of A4:A55, if found in "A10" for example, it then checks for the text "OFF" in "F10" (within the range of F4:F55). If it finds "OFF", it returns a "NO", if it doesn't find "OFF", it returns a "YES"."

Thanks much.
 
Upvote 0
My apologies Rick, I wrote that incorrectly.

It should be "What I need is for it to search for the specified text (located in A60) within the range of A4:A55, if found in "A10" for example, it then checks for the text "OFF" in "F10" (within the range of F4:F55). If it finds "OFF", it returns a "NO", if it doesn't find "OFF", it returns a "YES"."
I think the formulas I posted will do that, we just need to adjust the search range (you didn't tell us that originally) and replace "Specific Text" with the reference to the cell containing the specific text (again, you didn't say where the text being searched for came from). Try one of these (depending on the version of Excel you are using)...

Excel 2003
----------------------------
=IF(ISNUMBER(MATCH(A60,A4:A55,0)),IF(COUNTIF(INDIRECT(MATCH(A60,A4:A55,0)&":"&MATCH("Specific Text",A4:A55,0)),"OFF"),"YES","NO"),"")

Excel 2007 and above
----------------------------
=IFERROR(IF(COUNTIF(INDIRECT(MATCH(A60,A4:A55,0)&":"&MATCH("Specific Text",A4:A55,0)),"OFF"),"YES","NO"),"")
 
Upvote 0
I think the formulas I posted will do that, we just need to adjust the search range (you didn't tell us that originally) and replace "Specific Text" with the reference to the cell containing the specific text (again, you didn't say where the text being searched for came from). Try one of these (depending on the version of Excel you are using)...

Excel 2003
----------------------------
=IF(ISNUMBER(MATCH(A60,A4:A55,0)),IF(COUNTIF(INDIRECT(MATCH(A60,A4:A55,0)&":"&MATCH("Specific Text",A4:A55,0)),"OFF"),"YES","NO"),"")

Excel 2007 and above
----------------------------
=IFERROR(IF(COUNTIF(INDIRECT(MATCH(A60,A4:A55,0)&":"&MATCH("Specific Text",A4:A55,0)),"OFF"),"YES","NO"),"")


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)

Results:


  1. [*=1]Same as first formula


What am I missing? Thanks much.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top