Una validación de datos por aproximación...

Un tema recurrente en el mundillo es cómo conseguir una lista de validación acorde al texto introducido.
Si bien la solución que propongo aquí no es inmediata, es lo más parecido que he encontrado.

Se trata, entonces, de escribir una parte del texto (INICIALES!!!), para luego presionar la flecha del desplegable de la celda validada y que nos muestre aquellas palabras coincidentes con esas iniciales.
MUY IMPORTANTE!.. el rango con las celdas de las palabras deben estar ordenadas!!!

Partimos de un rango A2:A22 con unas palabras ordenadas en sentido ascendente y en D2 una celda validada con una fórmula que mostraré a continuación.
La idea es escribir en D2 parte (inicial o por la izquierda) de las palabras a mostrar... en la imagen siguiente se ve como al escribir 're' y presionar la flecha de validación aparecen las dos únicas palabras que comienzan por 're':

El misterio de este 'éxito' reside en una función DESREF anidada de una COINCIDIR (empleadas en más de una ocasión).
La función en cuestión:
=DESREF(ValidacionAprox!$A$2;COINCIDIR(ValidacionAprox!$D2&"*";ValidacionAprox!$A$2:$A$22;0)-1;;CONTAR.SI(ValidacionAprox!$A$2:$A$22;ValidacionAprox!$D2&"*"))

Por comodidad podemos genera un Nombre definido tipo fórmula:
Lista2: =DESREF(ValidacionAprox!$A$2;COINCIDIR(ValidacionAprox!$D2&"*";ValidacionAprox!$A$2:$A$22;0)-1;;CONTAR.SI(ValidacionAprox!$A$2:$A$22;ValidacionAprox!$D2&"*"))

para luego emplearlo en la validación:

La cuestión siguiente nos llevaría a preguntarnos.. ¿es posible conseguir algo similar sobre la premisa de buscar aquellas palabras que contengan el texto escrito en D2 (en lugar de sólo las que comiencen)??.
Sí, es posible.. con rangos auxiliares mediante fórmulas matriciales (en un post posterior escribiré al respecto); pero también con un poco de programación.

Añadimos los eventos siguientes en la ventana de código de la hoja de trabajo:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celda As Object
Dim i As Integer
Dim lista As String, elementos As String

'generamos la coleccion
Set unicos = New Collection
'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Range("A2:A22")
    If InStr(1, celda.Value, Target.Value, vbTextCompare) Then
        'cuando encuentre un item repetido, daría un error
        'que salvamos con la instrucción On Error Resume Next
        On Error Resume Next
        'por tanto, nuestra colección solo agrega elementos no repetidos
        'objeto.Add item, key, before, after
        'ocurre un error si una key especificada duplica la key de un miembro existente de la colección
        unicos.Add celda.Value, CStr(celda.Value)
        On Error GoTo 0
    End If
Next celda

'unir los datos en un literal...
For i = 1 To unicos.Count
    lista = lista & "," & unicos(i)
Next i
 
'quitar la primera coma
elementos = Mid(lista, 2)

If Not Intersect(Target, Range("D2")) Is Nothing Then
    With Range("D2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=elementos
    End With
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'comenzamos borrando la validación existente en D2
If Not Intersect(Target, Range("D2")) Is Nothing Then
    respuesta = MsgBox("Usar Lista existente??", vbYesNo)
    If respuesta = vbNo Then
        With Range("D2").Validation
            .Delete
        End With
    End If
End If
End Sub

Este código tiene una desventaja importante.. y es que se requiere una confirmación previa al seleccionar la celda D2, y que nos permite mantener o borrar la validación de celda existente o creada en un paso anterior con el evento _Change.

Obviamente no es lo más deseable.. pero al menos desplegamos aquellos elemento de nuestro rango que nos interesan...