Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# match formula

Posted on 2014-03-13
Medium Priority
223 Views
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
Question by:pma111
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 24

Accepted Solution

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

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

LVL 52

Assisted Solution

Rgonzo1971 earned 668 total points
ID: 39926222
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

LVL 33

Assisted Solution

Paul Sauvé earned 664 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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll