I have Data as List in Sheet1, which has the following
To Be Trained
Trained
Beginner
Moderate
Reviewer
Expert

I have Other Data as List in sheet2, which has the following

Leave
Other
ABC
DEF
HIM
HP
EP
AR
AP


If I select as Leave in G5 cell, then in other cell(F5) it should show as “Leave”
If I select Other in G5 cell, then in other cell(F5) it should show as “Other”
If I select ABC - AP* in G5 cell, then in other cell (F5) it should show as “Abstract/Review*”
* Note:
If I select ABC – AP from List, It should lookup into the Sheet1
for
Trained, Beginner & Moderate to be show as “Abstract”.
Reviewer, Expert to be show as” Review”
To Be Trained to be show as “To Be Trained”.

Please let me know which formula to be used.

I have attached sample excel file in (.zip) format for your kind reference.

Regards,
Asrar Ayub :)