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".

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1382001001[/TD]
[TD][/TD]
[TD]1362002002[/TD]
[/TR]
[TR]
[TD]1382002002[/TD]
[TD][/TD]
[TD]1362002003[/TD]
[/TR]
[TR]
[TD]1382002003[/TD]
[TD][/TD]
[TD]1362002004[/TD]
[/TR]
</tbody>[/TABLE]

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