x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 232

match formula

I have a basic spreadsheet with 11 columns. In columns A-C there are 500 rows of numbers (0-50), one number per cell. In columns E-K row 2 is a single row of 6 numbers (again 0-5), again one per cell.

In column D I need a formula I can copy down, which compares numbers in columns A-C (rows 1-500) which checks for matches with the numbers in row 2 E-K.

So for example if

A2 = 13
B2=6
C2=42

And the data in rows E-K were
E2 =1
F2 = 19
G2 = 18
H2=33
I2=40
J2=6

Then D2 should say "1", as there is 1 match.
0
pma111
3 Solutions

Commented:
Would this work in D2 coplied down:

=SUMPRODUCT(COUNTIF(A2:C2,E2:K2))
0

Commented:
Hi,

in D2 pls try

``````=COUNTIF(E2:J2,A2)+COUNTIF(E2:J2,B2)+COUNTIF(E2:J2,C2)
``````
I used E to J for 6 numbers

Regards
0

RetiredCommented:
Hi - don't forget to put the \$ sign in front of the 2s in (E2:J2,A2): i.e. E\$2:J\$2.

This way you can copy the formula to the 500 rows so the E2:J2 rows will not increase to E3:J3, E4:J4... as you copy in column D.

=COUNTIF(E\$2:J\$2,A2)+COUNTIF(E\$2:J2,B\$2)+COUNTIF(E\$2:J\$2,C2)

or

=SUMPRODUCT(COUNTIF(A2:C2,E\$2:K\$2))
Countif-example.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.