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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Here's another version:

=MMULT(--(AD3:AG3<>0),MMULT(--(AQ3:BI3=TRANSPOSE(AD3:AG3)),{1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1;0;1}))>0

and press Control+Shift+Enter when you enter it.

This probably won't work any better than FormR's formula though. When you enter the value of feet/inches in your cells, is it text? If so, are the values exactly the same, including spaces and symbols? If the value is a number formatted to show feet and inches, does the number match, including non-visible or rounded decimals?
 
Upvote 0
I did and it didn't.

Hi, as Eric has suggested, you need to make sure the values are exactly the same. You could test with a simple formula to help diagnose - i.e. does the formula:

=AD3=AQ3

Return TRUE?


Excel 2013/2016
ACADAEAFAGAHAIAJAKALAMANAOAPAQ
3TRUE3'-6 3/4"3'-6 3/4"
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
 
Upvote 0
Have I understood correctly?

Excel Workbook
ABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBI
3TRUE17236511852552823
4FALSE
5FALSE1723658176555234477882233517
interference
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
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