Does anyone know the formula to solve this problem.

I want to randomly assign a value to RandomName from the values from Random list, if there are same values in the Group column, I want the random value to be same in RandomName col.

CodePudding user response：

You could try:

Formula in B2 in ms365;

=XLOOKUP(A2,A$1:A1,B$1:B1,INDEX(D$2:D$5,RANDBETWEEN(1,4)),0)

Or for older version of Excel;

=IFERROR(INDEX(B$1:B1,MATCH(A2,A$1:A1,0)),INDEX(D$2:D$5,RANDBETWEEN(1,4)))