Auto Assign Serial Number to a text on other column in excel

Question

please see below image to understand what i want to do.

Excel Data Image

John has serial# 1001, when john is repeated on client column, serial# is same as it was on first entry. How can i automate this process in excel?

Answer 1

Put 1001 in A2. Then in A3 put:

=IF(B3<>"",IFERROR(INDEX($A$1:A2,MATCH(B3,$B$1:B2,0)),MAX($A$1:A2)+1),"")

And copy it down the column.

Answer 2

use the index/match formula:

=INDEX($A$1:$A3,MATCH(B4,$B$1:$B3,0))