Checking staff_name between 2 excel speadsheets

[philky001]

I have a spreadsheet with columns: Interested in staff_name and current role columns. Staff Name is one col. lastname,firstname(no space) and I am interested in the current roles
that have 'NULL' as their value. I have a second excel

Staff_name

Job_title

Group_name

Worker_role II

Comments

Current role

Extra roles currently

Smith,John NULL

Second excel: the staff_name here does have a space between the last name and first name, like Smith, John. The objective is to match the names of the staff and verify that if the Current role has NULL then is_all_worker_roles have a '1'

staff_name

is_all_worker_roles

 

[Crystalyzer]

You'll have to modify the ranges to suit your needs, but this formula will return the following:

• "Staff_Name Not Found" if the formula returns an error
• "NULL" if the Current role is blank
• Current Role if staff_name is found and current role is filled in

=IFS(ISERROR(VLOOKUP(TRIM(SUBSTITUTE(A4,",",", ")),Sheet2!$A$2:$F$4,6,0)),"Staff_Name Not Found",VLOOKUP(TRIM(SUBSTITUTE(A4,",",", ")),Sheet2!$A$2:$F$4,6,0)=0,"NULL","TRUE",VLOOKUP(TRIM(SUBSTITUTE(A4,",",", ")),Sheet2!$A$2:$F$4,6,0))