LOOKUPVALUE while filtering column

[Anonymous]

Hi,

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

The data table i have is like this:

row	Date	Employee Code	Join Date	Resign Date	Resign Reason	Join Reason
1	1/1/2020	123	1/1/2019
2	1/2/2020	123	1/1/2019	10/2/2020	Transfer corporation
3	1/2/2020	123	11/2/2020
4	1/3/2020	123	11/2/2020

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.

[harshnathani]

Hi @Anonymous ,

Share sample data and the expected output.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.