Excel Vlookup function to map duplicate and get the max occurence value

Question

[enter image descripenter image description heretion here](https://i.stack.imgur.com/iAQnF.png)

Table A contains values where A and C are duplicates. Table B should retrieve 12,33,14 for each of A,B, and C that are considered inputs to table A. By examining the occurrence in Table A and obtaining the most frequent value, Table B populates the output. In this instance, A had 11 and 12. However, 12 had 3 occurrences. So, it has a value of 12. Table B may have other values, such as D, or may not contain either A, B, or C. Is there any Excel function I can perform using this? I am confident that counting will work if you sort by counting. I'm trying to find a 1-line function.

Answer 1

Assuming there is no Excel version restriction for each tag given in the query. In cell D2, you could attempt the following:

=LET(A, A2:A8, B, B2:B8, cnts, COUNTIFS(A,A,B,B), ux, UNIQUE(A),
  out, MAP(ux, LAMBDA(x, TEXTJOIN(",",,UNIQUE(FILTER(B, (A=x) 
    * (cnts = MAX(FILTER(cnts, A=x)))))))), HSTACK(ux, out))

The formula takes into account the circumstance where one or more values occur the most frequently, as in the example of value C, where both 13 and 14 occur only once. To assemble the two values, we employ TEXTJOIN.

Here is the output: