Add or remove info to two lists in Sheet1 and Sheet2 and difference between two lists

Sheet1 to add BOO
=IF(LEN(A2)=4,"B000","B00")&A2

Sheet2
=--RIGHT(A2,5)

Sheet3
=A2=RIGHT(B2,5)*1
or
Conditional formatting (Select Column B)
=A1<>RIGHT(B1,5)*1

you are welcome. Thank you for feedback

AZ-XL, In sheet3 I have most of numbers in both lists but not in the same row.. I want to highlight those which are not exist in the other list... and in sheet2 why we put two "--" in the formula..??

In sheet3 to know whether list A matches or not
=IF(ISERROR(MATCH(A2,Sheet1!A:A,0)),"No Match","Match")
for list B
=IF(ISERROR(MATCH(B2,Sheet2!A:A,0)),"No Match","Match")

In sheet2 I put "--" because when Right function extracts last 5 letter its format being in text format. I put double minus to change their format to number. You can change "--" to others such as *1, +0, /1.

To highlight numbers you should use Conditional formatting. If you use excel 2007 then you cannot make reference to other sheets. For that you have to use name manager and then use this name in conditional formatting.

Originally Posted by alipezu

for what purpose can I use "*1, +0, /1" these functions???

When we use text functions (ex right, left, mid, etc) to extract numbers their format will be as text. For example. "000556" is in text format. To change format of it we should do mathematical operations. Double "--" changes value firstly to -556 then second minus makes it 556. As well as others. Listed operations do not change value of numbers but they change format.

It is possible to match in two different workbooks but these workbooks have to be open whenever you work on your file.

For example. If you want match from workbook2 you can use this function
MATCH(C2,'Workbook2'!$I:$I,0)