How to lookup a code prefix from another page and autofill with an alphanumeric sequence

I'm looking to populate a series of sequential codes based on a code prefix.

Sheet 1: Master Code list
Column A = Lookup Value
Column B = Code Prefix

Sheet 2 - Results page
Column A - list of lookup values - sorted in value order - The number of occurances varies for each value
Column B - Results list

I want to populate Column B in the second sheet with a sequential code list for each different value in column A. So for the first value it would have the prefix then proceed numerically (Prefix0001, Prefix0002, Prefix0003...) then for the next value its prefix in sequence (2ndPrefix0001, 2ndPrefix0002, 2ndPrefix003...), etc

I'm wondering if there is a way to do this all in one step or else if there is a way way to number each occurance of the lookup value in the second page, in which case the code can be concatenated easily by a lookup of the code.

I suppose on the back of this there is another query - is there a way to concatenate a number where the format is 000# where the concatenated value will retain the leading zeros? I've been trying and it is stripping them out.

Thanks!

That's fantastic - exactly what I was looking for but much more efficient than I expected. Thanks!