IF any of 10 columns equals any of 4 other columns

[Nanaia]

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

 

[Nanaia]

Hi, why not try it and let us know

I did and it didn't. I was trying to find out if it was supposed to.

 

[Eric W]

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?

 

[FormR]

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
	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3	TRUE	3'-6 3/4"													3'-6 3/4"
Sheet1

Cell Formulas
Range	Formula
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

 

[Peter_SSs]

Have I understood correctly?

Excel Workbook
	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
3	TRUE		17	23		65											1	18		52	5			5				28				23
4	FALSE
5	FALSE		17	23		65										8	17		65	55	23	44		77		88	22	33		5	17
interference