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

0 votes

[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 by Kithuzzz
• 38,010 points
335 views

1 answer to this question.

0 votes

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: excel output

answered Feb 4, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

MAX function in Excel: is it possible to provide the range by means of variables?

Try this: =MAX(INDEX(A:A,B2):INDEX(A:A,B3)) READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,700 points
401 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
736 views
0 votes
1 answer
0 votes
1 answer

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

answered Jan 5, 2023 in Others by narikkadan
• 63,700 points
625 views
0 votes
1 answer

Excel feet and inches to millimeters

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

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,497 views
0 votes
1 answer

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

answered Sep 30, 2022 in Others by narikkadan
• 63,700 points
810 views
0 votes
1 answer

Convert three letter country codes to full country names

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

answered Oct 16, 2022 in Others by narikkadan
• 63,700 points
711 views
0 votes
1 answer

How to categorize/classify numbers from different ranges using Excel?

I wouldn't advise utilizing nested IFs because ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,700 points
3,079 views
0 votes
1 answer
0 votes
1 answer

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

answered Nov 11, 2022 in Others by narikkadan
• 63,700 points
1,133 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP