This array formula modification of Oorang's formula will return the row number of the one last duplicated value

=MAX(--(COUNTIF(B1:B1000,B1:B1000)>1)*ROW(B1:B1000))

=INDEX(B:B,rowNumber,1) will return the value itself.

(Array formulas are confirmed with Ctrl-Shift-Enter (Cmd-Return for Mac))