Excel Formula to Check Equality

Objective

To create an Excel formula that checks if the values in the range B31:H31 are equal to any of the values in the range J3:J29. The output should be either TRUE if they are equal or FALSE if they are not.

Formula

The following array formula can be used:

=IF(SUMPRODUCT(--(B31:H31=TRANSPOSE(J3:J29)))>0, TRUE, FALSE)

Explanation of the Formula

1. SUMPRODUCT Function:

   • Computes the sum of products of corresponding ranges or arrays.
   • In this case, it counts how many times the values in B31:H31 match the values in J3:J29.

2. Equality Check (B31:H31=TRANSPOSE(J3:J29)):

   • The expression compares each element in B31:H31 with each element in J3:J29.
   • TRANSPOSE is used to align the data for comparison across a row (B31:H31) with a column (J3:J29).

3. Double Unary (--):

   • Converts the boolean results (TRUE/FALSE) into numeric values (1/0) so that SUMPRODUCT can aggregate them.

4. IF Function:

   • Checks if the result of SUMPRODUCT is greater than 0.
   • If true (indicating at least one match), it returns TRUE; otherwise, it returns FALSE.

Practical Example

Scenario

Assume the following data:

• B31:H31: {10, 20, 30, 40, 50, 60}
• J3:J29: {5, 15, 20, 25, 35}

Application of the Formula

1. Enter the values in B31:H31 and J3:J29 as specified.
2. In another cell (e.g., K31), enter the formula:

=IF(SUMPRODUCT(--(B31:H31=TRANSPOSE(J3:J29)))>0, TRUE, FALSE)

Outcome

• The formula will return TRUE since the value 20 in B31:H31 matches a value in J3:J29.

Conclusion

This formula effectively determines if any of the values in B31:H31 are equal to those in J3:J29, providing a boolean output. For enhanced learning and advanced techniques in data analysis, consider exploring the courses available on the Enterprise DNA Platform.