[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.

Feb 4 in Others 198 views

## 1 answer to this question.

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: • 63,180 points

## Excel MATCH function is not working on an array but works once directed to the matched value

According to the definition here: support.microsoft.com/en-us/office/… if you do ...READ MORE

## How to use Excel VLOOKUP function with words that begin with the letters AB

If you lookup "AB" in Excel, it ...READ MORE

## Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

To my knowledge, the only method to ...READ MORE

## Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

## Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

## Convert three letter country codes to full country names

Just create a list to be used in ...READ MORE