How to match the same data

H

hc

Hi, Could any one tell me how to match the client number from a column to
the d column? And also when the a column matches with d column number, the
b and c column nunbers also match with e f automatically.


a b c d e f
Client number name phone number
106 b 9584 3244 105 a 9825 5471
107 c 9784 2142 106 b 9584 3244
108 d 9880 2262 107 c 9784 2142
109 f 9669 2365 108 d 9880 2262
109 e 9789 1245
110 f 9669 2365
111 g 9550 2231
112 h 9323 5124
113 q 9984 1245
114 w 9956 2314
 
J

Jacob Skaria

In cell G1 enter the below formula and copy down as required. The formula
will check for a matching record and return either a Match or blank. Please
note that this is an array formula. You create array formulas in the same way
that you create other formulas, except you press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula>}"

=IF(ISNA(INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=D1)*($B$1:$B$100=E1),0))),"",IF(F1=INDEX($C$1:$C$100,MATCH(1,($A$1:$A$100=D1)*($B$1:$B$100=E1),0)),"Match",""))

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top