Fórmula genérica
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Resumen
Para encontrar el texto que aparece con más frecuencia en un rango, según los criterios que proporcione, puede usar una fórmula de matriz basada en varias funciones de Excel INDICE, COINCIDIR, MODO y SI. En el ejemplo que se muestra, la fórmula en G5 es:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
donde "proveedor" es el rango con nombre C5: C15 y "cliente" es el rango con nombre B5: B15.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
Trabajando desde adentro hacia afuera, usamos la función COINCIDIR para hacer coincidir el rango de texto con él mismo, dando a COINCIDIR el mismo rango para el valor de búsqueda y la matriz de búsqueda, con cero para el tipo de coincidencia:
MATCH(supplier,supplier,0)
Dado que el valor de búsqueda es una matriz con 10 valores, COINCIDIR devuelve una matriz de 10 resultados:
(1;1;3;3;5;1;7;3;1;5;5)
Cada elemento de esta matriz representa la primera posición en la que aparece el nombre de un proveedor en los datos. Esta matriz se alimenta a la función SI, que se usa para filtrar resultados solo para el Cliente A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
SI devuelve la matriz filtrada a la función MODO:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Observe que solo las posiciones asociadas con el Cliente A permanecen en la matriz. MODE ignora los valores FALSE y devuelve el número que ocurre con más frecuencia a la función INDICE como el número de fila:
=INDEX(supplier,1)
Finalmente, con el rango denominado "proveedor" como matriz, INDICE devuelve "Brown", el proveedor que aparece con más frecuencia para el Cliente A.
