Partial match between column

[jiaweilee]

How to find the partial match between two column. If there is the match from column C with Column A then in column B it will show " changed".

A	B	C
1382001001		1362002002
1382002002		1362002003
1382002003		1362002004

<tbody>
</tbody>

Tried this formula but it return #N/A.

=IF(MATCH("*"&RIGHT($C7,6),A7:A100,0),"changed","-"

 

[XLearner]

Hi,

You MUST catch the possible non-match that generates the #N/A error. Otherwise the error is returned by the formula before the value_if_true/value_if_false has a chance to be executed by the IF statement (this applies to any formula/function). So assuming your MACTH does what you expect change it for:

=IF( ISNA(MATCH("*"&RIGHT($C7,6), A7:A100,0)), "--", "changed" )

BTW I wonder why A7:A100 isn't A7:A$100. Maybe there's a good reason but just in case...

Regards
XLearner

 

[FDibbins]

MATCH("*"&RIGHT($C7,6),A7:A100,0)

this will always return #N/A error because RIGHT always returns text, even if it looks like a number, so you cannot use a wild card like that to find a number.

Try this instead...
=IF(ISNUMBER(SUMPRODUCT(MATCH("*"&RIGHT($C7,6),TEXT(A7:A100,"?"),0))),"Yes","No")
ARRAY entered, using CTRL SHIFT ENTER, not just enter