Match formula

[kumar1803]

Hello,

I have two column where I need to perform a match function or formula.
If the first 4 or more characters in both "A" and "B" column matches, I want to see the results in the third column.

For Example:

Column A
[TABLE="width: 350"]
<tbody>[TR]
[TD]MONTEFIORE MEDICAL CENTER-MARGARET KEALY[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


Column B

[TABLE="width: 346"]
<tbody>[TR]
[TD]MONTEFIORE MEDICAL CENTER[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


If the first 4 or more characters matches in both column, I like to see the results

 

[FranzV]

You can use de LEFT() function to extract the first 4 characters of each cell and then compare them. The complete formula for Column C would be:

=IF((LEFT($A2,4)=LEFT($B2,4)),LEFT($A2,4),"")​

You do not need columns D through H, but they might help you understand the formula piece by piece.
If you need your formula to be case sensitive, use the EXACT() function.

I hope it helps.

	A	B	C	D	E	F	G	H
Column A	Column B	Column C	LeftA	LeftB	IsMatch	CaseSensitive	Result
MONTEFIORE MEDICAL CENTER-MARGARET KEALY	MONTEFIORE MEDICAL CENTER	MONT	MONT	MONT	MONT
Hello	Hi		Hell	Hi

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]VERDADERO[/TD]
[TD="align: right"]VERDADERO[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]FALSO[/TD]
[TD="align: right"]FALSO[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD]=IF((LEFT($A2,4)=LEFT($B2,4)),LEFT($A2,4),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD]=LEFT($A2,4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD]=LEFT($B2,4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD]=$D2=$E2[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD]=EXACT($D2,$E2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H2[/TH]
[TD]=IF(F2,D2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]