Search code examples
excelgoogle-sheetsdynamic

Lookup vs Match vs countif

I may be over complicating this request as I know there is a way to get this value but I'm not an expert at excel or sheets. Here is my problem:

  • People can enter anything in one column but if there are matching text values I want to populate a cell below to say which is the most frequent input
  • If the inputs are less than 100% it should highlight the populate cell with a colour. In this case 2 out of 3 are Apples so Id like to change the populated cell a colour
  • If there are no inputs (column B) then it will spit out "no inputs"
  • The same premise with numbers as well.

Basically I want to compare inputted values/numbers against each other and get the most frequent. Thanks

Column A
Apples
Apples
Oranges
--------
Apples
Column B
--------
"no inputs"
Column C
1
2
1
--------
1

I have tried a multitude of things like VLOOKUP, XLOOKUP, MATCH and COUNTIF. The closest I got it to work was using the following cell formula =XLOOKUP(H3,$H$3:$H$5,$H$3:$H$5,"")

Solution

  • The following formula tackles points 1, 3, and 4, using column A as an example:

    =IF(COUNTBLANK(A1:A3)>0, "no inputs", INDEX(A1:A3,MODE(MATCH(A1:A3,A1:A3,0))))

    Notes:

    1. You can change the behavior of the output if the column is only half-filled with inputs by changing the >0 criterion for the COUNTBLANK.
    2. Further adaptation may be needed to fine-tune the desired response if you have inputs with the same frequency, or if all inputs are different.

    Conditional formatting can be used to tackle point 2. The cell will turn a different color if the formula like the one below is true. Again using Column A as an example, where a full list is assumed to be 3 inputs long and A5 is the cell where the most frequent input is:

    =COUNTIF(A1:A3,A5)<3