comparing cells and copying data based on match or failure

[Gnome]

Hi all,
I'm not even sure how to start this one - I'll do my best to explain.
I have one worksheet called "sheet1" and it contains the below data

Title	User Name	Status
Backup	123_nissanseed
Authentication	ABCDEF\123_subwaywilderness
misc.	123_hyundaicomedy
copy	abc\123_samsungminnow

I then have "sheet2" which contains the following

Title	User Name	Status
Backup		On
Authentication	123_subwaywilderness	Off
misc.	123_hyundaicomedy	On
copy	123_samsungminnow	On

Is there a way I can search the username field in sheet2 and if it matches sheet1 - highlight the cell green in sheet 1 and copy the status field across also.

The ***\ only exists in sheet1 and can be ignored, it’s the second half that will match and 123_will be always be there in both sheets

If no match is found, the cell in sheet1 needs to be highlighted red.
The examples in username isn't exhaustive and updates... is this a possible thing to achieve?

 

[Gnome]

perhaps easier - if anything in sheet1 column A matches with anything in sheet2 column B then copy value from column C ( next cell over) back to sheet1 column B

 

[gargamalebarbosa]

I hope i understand right. try this one.

 

[Joe4]

In order to higlight the found entries on Sheet 1 green, assuming that your data starts up in cell A1 on each sheet, do the following:
1. On Sheet1, select cell B2 down to the botton of column B
2. Go to Conditional Formatting
3. Select the "New Rule" option
4. Select the "Use a formula to determine which cells to format" option
5. Enter in the following formula:

=COUNTIF(Sheet2!$B:$B,$B2)>0

6. Choose the green formatting option
7. Click OK.

Then, to highlight the unfound ones red, follow the exact same steps, except use this formula in step 5:

=COUNTIF(Sheet2!$B:$B,$B2)=0

and choose the red formatting option.

Then, to bring back the status in column C, enter this formula in cell C2 on Sheet1 and copy down for all rows:

=IFERROR(VLOOKUP($B2,Sheet2!B:C,2,0),"")