• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search for 2 criteria in 2 ranges, return yes no

A

atypicalv

New Member
Greetings-

Having trouble creating a formula that checks for 2 distinct criteria in a range within 2 different columns to determine if they are true or false (each criteria is in a range in different columns). I hope someone here can help.


It needs to function like this:

If criteria 1 is TRUE it then proceeds to check the 2nd criteria. If the 2nd criteria is TRUE, it will return a “NO”, if the 2nd criteria is FALSE, it will return a “YES”. And if the 1st criteria is FALSE, it will not proceed to check the 2nd, and it will return a “NO”.


Example:

-Search in A1:A60 for the same as A75 (search criteria 1). If TRUE, then proceed to search for 2nd criteria of “OFF” in F1:F60. If FALSE, return a “NO”, do not proceed to search for criteria 2.


-If search criteria (“OFF”) is TRUE in F1:F60, return a “NO”, if it is FALSE, return a “YES”.


The following formulas work, but each have an issue:

{=IF(A1 =A75,IF(F1 ="OFF","NO","YES"),"NO")} - this works for single cells, but need it to work for range


{=IF(COUNTIF(A1:A60,A75),IF(COUNTIF(F1:F60,"OFF"),"NO","YES"),"NO")} – this works fine, however it stops working if you do the following specific sequence: with the search criteria in column "A" of "Brick", say "A40", and something other than "OFF" in "F40", the result is "YES" (correct). If you change "F40" to "OFF" - the result is "NO" (correct), and then change "A40" to something other than "Brick", it still returns "OFF" (correct). But now if you change "A25" to "Brick", and "F25" 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 "Brick" in a cell, it will give the appropriate return of "NO" if the corresponding "F" column cell has "OFF", or a "YES" if the "F" column cell doesn't have "OFF".


{=IF(SUM(--ISNUMBER(FIND(A66,A6:A57,1))*(--ISNUMBER(FIND("OFF",B6:B57,1)))),"NO","YES")} – this works, except if criteria 1 is not present in the “A” range, it does not stop and return a “NO”, it returns a “YES”.


Thanks in advance for any assistance.
 
Atypicalv


Firstly, Welcome to the Chandoo.org forums


Can you post a sample file with some data we can review?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


I note you have entered the formula as Array Formula? Is there any particular reason for that ?
 
Thanks for the welcome and reply Hui.


Sample workbook is here: https://www.box.com/s/jl5k2zunxs1sm610prtz


"Data" is the sheet to look at, and I know the sheet is not a typical format.


The current formulas in A64:A83 can be ignored, they are from another workbook. A63:A83 would be the search criteria 1, and each day would have its own field of search criteria beneath it. The job types in the "A" column would not have duplicate search criteria, as it does currently, those fields have just been toggled as the sheet has been tested. e.g. "L Saute" would only be in the "A" column 1 time.


As to why it's entered as an array; no specific reason other than example formulas I've been playing with have been array formulas. I'm an excel novice so am learning a whole lot on this project.


Thanks much.
 
Can you confirm this logic:


for Cell B64


If the name in A64 L Saute is in A4:A55 and B4L:B55 = "OFF" then B64 = No

If the name in A64 L Saute is in A4:A55 and B4L:B55 = "ON" then B64 = Yes

If the name in A64 L Saute is not in A4:A55 then B64 = No
 
I think in B64 you want

=IF(MAX(($A$4:$A$55=$A64)*(B$4:B$55="OFF")),"NO","YES") Ctrl+Shift+Enter
 
The logic is correct.


I used the provided formula {=IF(MAX(($A$4:$A$55=$A64)*(B$4:B$55="OFF")),"NO","YES")}

- all works, EXCEPT if "L Saute" is not in A4:A55, it is still returning a "YES".


It's so close, just not all the way there yet.


Thanks, let me know if there is a tweak needed.
 
Try:

=IF(MAX(($A$4:$A$55=$A64)*(B$4:B$55<>"OFF"))>0,"Yes",IF(MAX(--($A$4:$A$55=$A64)),"NO","Yes")) Ctrl+Shift+Enter
 
OK, I put this in: {=IF(MAX(($A$4:$A$55=$A64)*(B$4:B$55<>"OFF"))>0,"Yes",IF(MAX(--($A$4:$A$55=$A64)),"NO","Yes"))}


Unfortunately getting the same result; returns "YES" when the search criteria 1 is NOT present in the range A4:A55


BUT! I altered it to this, and it seems to be working exactly as needed:


{=IF(MAX(($A$4:$A$55=$A64)*(B$4:B$55<>"OFF"))>0,"Yes",IF(MAX(--($A$4:$A$55<>$A64)),"NO","YES"))}


Thanks Hui so much! I wouldn't have gotten this on my own, and had already spent too many hours trying to get it sorted.


Cheers!
 
You must log in or register to reply here.
Back
Top