Excel formula to copy 1st Column A value for successive identical Column B values; then repeat for next Column B value

Question

Below, on the left (headed "BEFORE") is a small extract from a large (20,000 row) Excel spreadsheet. The 2 columns have been sorted on the "Name" column value.

I want the "ID" column value to be the same for each distinct entry in the "Name" column; for example by taking the first-occurring "ID" column value for each name and writing that value into the "ID" column for each successive occurrence of the same distinct Name. The extract on the right (headed "AFTER") shows the result I'm looking for.

Is there a way to do this with an Excel formula?

Answer 1

As @BigBen suggested you can use INDEX/MATCH combination like below-

=INDEX($A$2:$A$8,MATCH(E2,$B$2:$B$8,0))

You can use XLOOKUP() if you have Excel365.

=XLOOKUP(E2,$B$2:$B$8,$A$2:$A$8)