Search code examples
arraysexcelif-statementmatchmode

Excel, Frequent string value with IF condition

I would like to find the modal area code for each ID number in excel. I have 2 columns 

ID no.            Area Code
1                 ABC
1                 ABC
1                 ABC
1                 DEF
2                 HIJ
2                 HIJ
2                 KLM

So far I am finding the mode of the whole column using:

=(INDEX(B:B,MODE(MATCH(B:B,B:B,0))))

But I would like all ID no. 1 area codes to be ABC and ID no. 2 to be HIJ

Any advice would be great! Thanks

Solution

  • You could use a lookup table with the following array formula:

    =INDEX($B$2:$B$13,MODE(IF($A$2:$A$13=D2,MATCH($A$2:$A$13,$A$2:$A$13,0))))

    You enter array formulas by pressing Ctrl + Shift + Enter to enter the formula

    In the example shown below the formula would go in E2 next to the first listed ID and then you would drag it down for all the IDs in the adjacent column.

    Example:

    Example