Matching formula

[crag8119]

Hi,

Please could someone help me some match the data in columns 'Transaction Number' and ;Line Description':
[TABLE="width: 883"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Transaction Number[/TD]
[TD]Transaction[/TD]
[TD]Line Description[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Rejection for Invoice[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]5-Nov-18[/TD]
[TD]Miscellaneous_Negative_Entry_For_NEW[/TD]
[/TR]
[TR]
[TD]T1173777-NEW[/TD]
[TD="align: right"]26-Nov-18[/TD]
[TD]Miscellaneous_Negative_Entry_For_NEW[/TD]
[/TR]
[TR]
[TD]T1175778-NEW[/TD]
[TD="align: right"]26-Nov-18[/TD]
[TD]75006801 | T1175778[/TD]
[/TR]
[TR]
[TD]T1173776-NEW[/TD]
[TD="align: right"]26-Nov-18[/TD]
[TD]Miscellaneous_Negative_Entry_For_NEW[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to match the first 8 characters in column 'Transaction Number' to the value in 'Line Description'.

I have tried:=IF(ISNA(MATCH(LEFT(C5,8),RIGHT(E1:E100,FIND("|",E1:E100)-1)),0)),"No Match",C5)

however it just errors. I am trying to match the first 8 characters under 'Transaction Number' to the value after "|" in the 'Line description' column.

Many thanks

 

[FormR]

Re: Help needed for matching formula

I am trying to match the first 8 characters under 'Transaction Number' to the value after "|" in the 'Line description' column.

Hi, you could try:

=IF(ISNUMBER(MATCH("*| "&LEFT(C5,8),$E$1:$E$100,0)),C5,"No match")

 

[crag8119]

Re: Help needed for matching formula

Hi, you could try:

=IF(ISNUMBER(MATCH("*| "&LEFT(C5,8),$E$1:$E$100,0)),C5,"No match")

Great thank you,

Is there a way I can highlight both column C and E when a match is found?

 

[FormR]

Re: Help needed for matching formula

Is there a way I can highlight both column C and E when a match is found?

Hi, what do you mean by highlight? Have you looked at conditional formatting?