Macro, obtener dato según su valor de lista de datos

Bueno yo lo he hecho con VBA porque me apaño mejor, aunque casi seguro que se puede hacer con formulas array. Ve al editor de VBA (Alt+F11) inserta un módulo (Insert/Module) y copia el siguiente código.

Option Explicit
Sub CalculaCentrales()
Dim r As Range
Dim s As Range
'Borro el rango k excepto cabecera (aunque luego se machaca con el filtro)
Range("k5", Range("k5").End(xlDown)).Clear
'Copia las centrales únicas a K
'Empieza en D4, porque este filtro usa la fila de cabecera y si D4 está repetida no la
'consideraría
'en D4 debe haber texto de cabecera que se copia además en K4
Set r = Range("D4", Range("D4").End(xlDown))
r.AdvancedFilter Action:=xlFilterCopy, _
 CopyToRange:=Range("K4"), Unique:=True
Set s = Range("K5", Range("K5").End(xlDown))
'Recorre la lista de D y marca en rojo en K las que tienen 0
Call ColoreaK(r, s)
'Marca en D los que estan en rojo en K
Call MarcaRojos(s, r)
'Marca los verdes según los ceros y los que están en rojo
Call ColoreaD(s)
End Sub
Sub ColoreaK(r As Range, s As Range)
'Colorea en rojo las centrales del rango r(K) que tienen 0 en s(D)
Dim c As Range
Dim d As Range
For Each c In r 'como r tiene cabecera se da una vuelta inutil
 If Cells(c.Row, 7).Value = 0 Then 'tiene un cero
 For Each d In s
 If c.Value = d.Value Then 'es la que busco
 d.Font.ColorIndex = 3
 Exit For 'y salgo ya que no hay mas
 End If
 Next d
 End If
Next c
End Sub
Sub MarcaRojos(s As Range, r As Range)
'Colorea en s los rojos de r
Dim c As Range
Dim d As Range
For Each c In r
 For Each d In s
 If (d.Font.ColorIndex = 3) And (c.Value = d.Value) Then 'es la misma y la coloreo
 c.Font.ColorIndex = 3
 End If
 Next d
Next c
End Sub
Sub ColoreaD(r As Range)
'Colorea en verde las siguientes que no estan a 0 ni en rojo
Dim i As Integer
Dim j As Integer
Dim max As Integer
max = r.End(xlDown).Row + 4
For i = max To 5 Step -1
 If Cells(i, 7).Value = 0 Then 'esta a cero
 For j = i To 5 Step -1
 If (Cells(j, 4).Font.ColorIndex <> 3) And _
 (Cells(j, 4).Value <> 0) Then 'me vale y la pongo verde
 Cells(j, 4).Font.ColorIndex = 4
 Exit For
 End If
 Next j
 End If
Next i
End Sub

La macro a ejecutar es CalculaCentrales y puedes ejecutarla manualmente, asociándola a una tecla o cuando haya un cambio en los datos de la columna D de la hoja actual. Esto último, quizá algo más complejo se hace introduciendo en la hoja, no en el módulo antes creado el código a continuación. Para introducir código en la hoja, seleccionala en el panel izquierdo del editor de VBA, pulsa el botón derecho y escoge View Code. En la parte derecha la ventana en blanco es para copiar lo siguiente:

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
If Not Intersect(target, Range("D:D")) Is Nothing Then
 ' si ha cambiado D calculo las centrales
 Call CalculaCentrales
End If
Application.EnableEvents = True
End Sub

Es posible que tengas que adaptar algo tu código.