Texto predictivo en celdas con Validación de datos

Hace unos días vi un vídeo de Leila Gharani en su canal de YouTube (recomendado!!) donde daba un uso interesante a las funciones desbordadas (ORDENAR, FILTRAR...) para conseguir el efecto de un 'texto predictivo' con la validación de datos.
Como aún no son de uso generalizado estas funciones pensé si era posible lograr algo similar con las funciones 'de toda la vida'... y llegué a una solución :O

Partiremos de una Tabla con un listado de frutas...

Comenzaremos creando nuestra fórmula deseada que es capaz de listar por aproximación aquellas frutas que contengan el texto escrito en la celda, y la incluiremos en un nombre definido (que he llamada 'ndCoincidencias'):
=SI.ERROR(INDICE(Tabla1[Listado Frutas];K.ESIMO.MENOR(SI(NO(ESERROR(ENCONTRAR(Hoja1!$D3;Tabla1[Listado Frutas])));FILA(Tabla1[Listado Frutas])-1;"");FILA(INDIRECTO("1:"&CONTAR.SI(Tabla1[Listado Frutas];"*"&Hoja1!$D3&"*")))));"")

Esta fórmula, con comportamiento matricial dentro del nombre definido, es capaz de listar aquellas frutas que contengan el texto buscado y escrito en una celda... en el ejemplo D3.
Es bastante elaborada... pero la clave es localizar las frutas coincidentes con la función ENCONTRAR. El resto básicamente identifica el número de la fila donde se encuentra la fruta para luego ordenarla (para que aparezcan 'arriba' en el listado de coincidencias), que conseguimos con K.ESIMO.MENOR.
Finalmente INDICE retorna los nombres de las frutas, en lugar de los número de filas.

La fórmula desbordada aportada por Leila Gharani en su vídeo era algo así:
ORDENAR(FILTRAR(Tabla1[Listado Frutas];ESNUMERO(ENCONTRAR($E3;Tabla1[Listado Frutas]));""))

Mucho más simple jeje...

Lo importante, en cualquier caso, no es la fórmula empleada, son los pasos siguientes.

Con el nombre definido ya creado, en mi caso:
ndCoincidencias=SI.ERROR(INDICE(Tabla1[Listado Frutas];K.ESIMO.MENOR(SI(NO(ESERROR(ENCONTRAR(Hoja1!$D3;Tabla1[Listado Frutas])));FILA(Tabla1[Listado Frutas])-1;"");FILA(INDIRECTO("1:"&CONTAR.SI(Tabla1[Listado Frutas];"*"&Hoja1!$D3&"*")))));"")

Fijémonos que la referencia a $D3 es mixta!!. Esto permite que al arrastrar la fórmula, en cada celda se refiera a lo escrito en su celda correspondiente.

Ahora trasladaremos a la hoja de cálculo los resultados...

Teniendo presente que mi rango de trabajo es D2:D12, en las celdas F2:F12 escribimos:
=TRANSPONER(ndCoincidencias)

Accederemos ahora, tras seleccionar D2:D12, a la herramienta de Validación de datos tipo Lista.
IMPORTANTE!!!... Cambiamos y desmarcamos el check en la pestaña 'Mensaje de error' que indica: Mostrar mensaje de error si se introducen datos no válidos..
Este paso es fundamental ya que permite escribir en la celda valores que no cumplan la regla de validación!!.

Sin salir de la herramienta vamos a la pestaña de 'Configuración', donde elegimos Permitir tipo Lista y en su Origen escribimos la referencia 'desbordada' usando la almohadilla o numeral #
=$F2#

Listo... probemos el sistema ;-)
Escribimos en D2 un par de letras cualquiera, al pulsar el desplegable comprobaremos que solo veremos aquellas frutas que contengan las letras/sílabas escritas, esto es, algo muy parecido a un 'comportamiento predictivo'.

Por ejemplo, al escribir 'fr' y pinchar en el desplegable aparecen las frutas que contienen 'fr' que en el ejemplo son: fresa y frambuesa

El efecto es interesante ya que escribimos un texto, al pinchar el desplegable se valida ese texto, y NO salta error alguno puesto que desmarcamos, en su momento, la opción de Mostrar mensaje de error si se introducen datos no válidos., de forma inmediata, la fórmula agregada en el nombre definido 'trabaja' y retorna la matricial (o desbordada, según el caso) con las frutas coincidentes.. que a su vez, y nuevamente de manera inmediata, alimenta la validación de datos!!.

Interesante cuanto menos!!

Una última nota para terminar... negativa :'(
Este método no es aplicable a las Tablas.