Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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
Asked:
pma111
3 Solutions
 
SteveCommented:
Would this work in D2 coplied down:

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

in D2 pls try

=COUNTIF(E2:J2,A2)+COUNTIF(E2:J2,B2)+COUNTIF(E2:J2,C2)

Open in new window

I used E to J for 6 numbers

Regards
0
 
Paul SauvéCommented:
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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now