Match Function

[chaz0789]

Hi,
I'm trying to figure out a way to do a simple data check. I have two rows of data, both have the exact same numbers/Text If one of the numbers/Text changes i need it to post a text of "Not A Full Match" if both data sets a equal i need it to say "Match". The formula I'm currently using works, but it only works for 1 value. I need it to work for an array of data. Any ideas?

=IF(ISERROR(MATCH(N21,O21:O57,0)),"Not A Full Match","Match")

 

[63falcondude]

How about something like this? You will have to change the ranges in the formula to match your data. Note that this example is "Not A Full Match" because they are not exactly the same (case matters).

Excel 2010
	A	B	C	D	E	F	G
1		1	2	3	a	b	c
2		1	2	3	a	B	c
3
4	Not A Full Match
Sheet1

Cell Formulas
Range	Formula
A4	=IF(SUMPRODUCT(--EXACT(B1:G1,B2:G2))=COUNTA(B1:G1),"Match","Not A Full Match")

 

[chaz0789]

This seems to be working. However, part of the data is using index/match function. So when I don't have a match it produces a #N/A, so instead of "Not A Full Match" i get #N/A.

 

[Marcelo Branco]

Hi,
I'm trying to figure out a way to do a simple data check. I have two rows of data, both have the exact same numbers/Text If one of the numbers/Text changes i need it to post a text of "Not A Full Match" if both data sets a equal i need it to say "Match". The formula I'm currently using works, but it only works for 1 value. I need it to work for an array of data. Any ideas?

=IF(ISERROR(MATCH(N21,O21:O57,0)),"Not A Full Match","Match")

Question:
Suppose two data ranges like these

Text1​	3​
Text2​	Text1​
Text3​	Text2​
1​	Text3​
2​	1​
3​	2​

<tbody>
</tbody>

Is this a Match or a Not Full Match?

M.

 

[63falcondude]

ADVERTISEMENT

This seems to be working. However, part of the data is using index/match function. So when I don't have a match it produces a #N/A, so instead of "Not A Full Match" i get #N/A.

What if you change the formula to =IFERROR(IF(SUMPRODUCT(--EXACT(B1:G1,B2:G2))=COUNTA(B1:G1),"Match","Not A Full Match"),"Not A Full Match") ?

 

[chaz0789]

What if you change the formula to =IFERROR(IF(SUMPRODUCT(--EXACT(B1:G1,B2:G2))=COUNTA(B1:G1),"Match","Not A Full Match"),"Not A Full Match") ?

That works perfectly!!! Thank you for the help.

 

[63falcondude]

That works perfectly!!! Thank you for the help.

Glad to help!