[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, 2023 in Others 368 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,720 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

## Large excel file, I'm trying to find duplicates and copy the rest of the cells across the duplicate to the duplicate it finds

Copy the column from the other sheet ...READ MORE

## Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE