LOOKUPVALUE while filtering column

Hi,

I need help to lookup value while having the columns filtered.

The data table i have is like this:

I want to identify the Join reason for employees  to single out employees who are transferred from those new joiners.

What i tried to do was to create another column with date & employee code, and another column using PREVIOUSMONTH(Date)&employee code.
Then i use iferror(if(lookupvalue(resign reason, date&employeecode,Previousmonth(date)&employee code)="transfer corporation","Transferred",""),"") to find the resign reason for row 3.

This would typically work when employees resign date was at the last day of the month and the join date was the start of next month, however i got stuck when the join date for row 3 was in the same month as join date for row 2.

I wanted to use
iferror(
if(
or(lookupvalue(resign reason, date& employee code,date&employee code, resign reason, not(blank())),
lookupvalue(resign reason, date& employee code, previous month(date)&employee code)) = "transfer corporation",
"Transferred",""),"")

However it just doesnt work with not(blank()), please advice if there are any other ways.

Thank you.