Solved

match formula

Posted on 2014-03-13
3
206 Views
Last Modified: 2014-03-25
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
Comment
Question by:pma111
3 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 167 total points
ID: 39926216
Would this work in D2 coplied down:

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

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 167 total points
ID: 39926222
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
 
LVL 31

Assisted Solution

by:Paul Sauvé
Paul Sauvé earned 166 total points
ID: 39926608
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now