IF any of 10 columns equals any of 4 other columns

Nanaia

Active Member
Joined
Jan 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
What is the must efficient formula to create a TRUE/FALSE result for the following without having to write a huge step by step OR formula - or is that my only choice?

IF AQ3=AD3, TRUE or
IF AQ3=AE3, TRUE or
IF AQ3=AF3, TRUE or
IF AQ3=AG3, TRUE or
IF AS3=AD3, TRUE or
IF AS3=AE3, TRUE or
IF AS3=AF3, TRUE or
IF AS3=AG3, TRUE or
IF AU3=AD3, TRUE or
IF AU3=AE3, TRUE or
IF AU3=AF3, TRUE or
IF AU3=AG3, TRUE or
IF AW3=AD3, TRUE or
IF AW3=AE3, TRUE or
IF AW3=AF3, TRUE or
IF AW3=AG3, TRUE or
IF AY3=AD3, TRUE or
IF AY3=AE3, TRUE or
IF AY3=AF3, TRUE or
IF AY3=AG3, TRUE or
IF BA3=AD3, TRUE or
IF BA3=AE3, TRUE or
IF BA3=AF3, TRUE or
IF BA3=AG3, TRUE or
IF BC3=AD3, TRUE or
IF BC3=AE3, TRUE or
IF BC3=AF3, TRUE or
IF BC3=AG3, TRUE or
IF BE3=AD3, TRUE or
IF BE3=AE3, TRUE or
IF BE3=AF3, TRUE or
IF BE3=AG3, TRUE or
IF BG3=AD3, TRUE or
IF BG3=AE3, TRUE or
IF BG3=AF3, TRUE or
IF BG3=AG3, TRUE or
IF BI3=AD3, TRUE or
IF BI3=AE3, TRUE or
IF BI3=AF3, TRUE or
IF BI3=AG3, TRUE
If none of these are true then false
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I just realized I need to add that this needs to be checked if any of the above mentioned cells equals more than zero. If both cells equals zero then false.
 
Upvote 0
Hi, here is one option you can try:

=SUMPRODUCT(COUNTIFS(AD3:AG3,"<>0",AD3:AG3,CHOOSE({1,2,3,4,5,6,7,8,9},AQ3,AS3,AU3,AW3,AY3,BC3,BE3,BG3,BI3)))>0
 
Upvote 0
If by "step by step" you mean a macro, then what about this...

Code:
Sub TEST()
    Dim i As Long, x As Long
    For i = 43 To 62 Step 2
        For x = 30 To 33
            If Not Cells(3, i).Value = Cells(3, x).Value Then
                MsgBox "False"
                Exit Sub
            End If
        Next
    Next
End Sub
 
Upvote 0
Maybe a brief explanation of why I'm trying to do this would help. We received CSV's from a different department. These CSV's are used to create a product. They just did a major reorganization of that department. The new folks are putting designs on top of other designs. What I'm trying to do is create a column, or series of columns that double checks the other departments work to make sure a product is not created with two designed that overlap each other. If there is no design (the CSV value is the center of the design) the cell value is zero. If, for example, AQ3 and AD3 both equal zero, I want a FALSE result because I'm looking for interference and two zeros means no interference. If both AQ3 and AD3 equal the same number then I want the result to be TRUE because there is interference.
I hope this helps clarify.
 
Upvote 0
If I was doing a task like that, I would not try to encode the results of all comparisons into a single formula.
If the formula returns TRUE, you still need to find out which of the possible permutations is driving that result - this formula won't tell you.
Also, if 2 or more permutations generate a TRUE result, the formula won't tell you that.
Perhaps consider using a matrix with all the elements along the top and down the side, and have separate formulas to analyse each permutation, and highlight any match.
 
Upvote 0
If, for example, AQ3 and AD3 both equal zero, I want a FALSE result because I'm looking for interference and two zeros means no interference.


Excel 2013/2016
ACADAEAFAGAHAIAJAKALAMANAOAPAQ
3FALSE00
Sheet1
Cell Formulas
RangeFormula
AC3=SUMPRODUCT(COUNTIFS(AD3:AG3,"<>0",AD3:AG3,CHOOSE({1,2,3,4,5,6,7,8,9,10},AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3)))>0


If both AQ3 and AD3 equal the same number then I want the result to be TRUE because there is interference.


Excel 2013/2016
ACADAEAFAGAHAIAJAKALAMANAOAPAQ
3TRUE55
Sheet1
Cell Formulas
RangeFormula
AC3=SUMPRODUCT(COUNTIFS(AD3:AG3,"<>0",AD3:AG3,CHOOSE({1,2,3,4,5,6,7,8,9,10},AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3)))>0


I missed a cell reference first time around - but the suggested formula seems to return the results you say you are expecting
 
Upvote 0
If I was doing a task like that, I would not try to encode the results of all comparisons into a single formula.
If the formula returns TRUE, you still need to find out which of the possible permutations is driving that result - this formula won't tell you.
Also, if 2 or more permutations generate a TRUE result, the formula won't tell you that.
Perhaps consider using a matrix with all the elements along the top and down the side, and have separate formulas to analyse each permutation, and highlight any match.

The only thing I'm trying to get the TRUE result to tell me is that there is an issue so we know to investigate. Our investigation will find the issue and we can kick the CSV back to the other department if it's a major error or fix it ourselves if it's minor.
 
Upvote 0
Will the same formula apply if the values in the cells are in feet and inches? i.e. 3'-6 3/4"
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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