Matching formula

[crag8119]

Hi,

Please could someone help me some match the data in columns 'Transaction Number' and ;Line Description':

Transaction Number	Transaction	Line Description
Date
		Rejection for Invoice
	5-Nov-18	Miscellaneous_Negative_Entry_For_NEW
T1173777-NEW	26-Nov-18	Miscellaneous_Negative_Entry_For_NEW
T1175778-NEW	26-Nov-18	75006801 | T1175778
T1173776-NEW	26-Nov-18	Miscellaneous_Negative_Entry_For_NEW

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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?