Formula to Capture Incomplete records

[Lasantha]

Dear Team,

Kindly look in to the attached excel file.

I need a formula to capture all employees who has first location ID as 1 and last location ID as 1.

Example- employee Tuan Sarjun Hassim

1st location ID is 1, and last location ID also 1, so i need capture these individuals separately in column J as "INCOMPLETE".

Example- employee Shiron Fernando

1st location ID is 1, and last location ID also 1, so i need capture these individuals separately in column J.

kindly look in to the highlighted records.
Please review and let me know if you need further details.

Thank you.

 

[chirayu]

Please note that the file uses named range, the formula is a CSE formula.

 

[Deepak]

Is this...

Paste in J2 > Copy Down

=IF(VLOOKUP(I2,$B$1:$E$79,4,0)=LOOKUP(,-1/(I2=$B$1:$B$79),$E$1:$E$79),"INCOMPLETE","")

 

[Lasantha]

Please note that the file uses named range, the formula is a CSE formula.

Thank you

 

[Lasantha]

Is this...

Paste in J2 > Copy Down

=IF(VLOOKUP(I2,$B$1:$E$79,4,0)=LOOKUP(,-1/(I2=$B$1:$B$79),$E$1:$E$79),"INCOMPLETE","")

Thank you.