Cell F2 → =IF(ISERROR(VLOOKUP(G2,$A$2:$B$21,2,0)),EOMONTH(DATE(E2,D2,1),0),VLOOKUP(G2,$A$2:$B$21,2,0))
Cell H2 → =EOMONTH(DATE(E2,D2,1),0)
Why cells in column F which are FALSE is blank ?
Is there any reason to use IF(ISERROR(X);Y;X) instead of IFERROR(X;Y)?
It is unclear what seems wrong to you. In F2, a lookup is made in $A$2:$A$21 for the value in G2 (0000001), which is found in A2, and its corresponding B2 is returned (empty), not an error; so of course, the IFERROR returns FALSE, and then the “THEN” expression is not evaluated, but instead “ELSE” clause, which is the same VLOOKUP again, which returns an empty string, which you correctly see in F2.
@lonk: I don’t understand your question. What is “different”? What I wrote above (both in my comment, and in my answer) is also true for Excel - but you need to elaborate in your questions: the link you provided above looks like you expect me to study that text carefully and understand in what part that contradicts with something I wrote?
Sorry for many comments.
I misunderstood.
ISERROR(VLOOKUP(G2,$A$2:$B$21,2,0)) searches for Staff ID, not the Resignation date.
It will give the result of 12/31/2019 for the ID in column G is not 0000001-0000020.
Yes, right! 