the formula you are looking for is (in d3): =IF($C$2:$C$999=$A4;$A$2:$A$999;"") +ctrl+shift+enter
make sure you enter it as an array
(NOTE: you may also need to change ; to , depending on your regional settings)
This will return content of A if data in C matched current Ai. You can see only one result now, since the result is array, but if you use Transpose() and select multiple cells in same row, then with F2 then ctrl+shift+enter you will see all results in a row!
Ex: select D3:K3, press F2, write formula: =Transpose(IF($C$2:$C$999=$A4;$A$2:$A$999;"")), press ctrl+shift+enter...
(again you may need to write: =Transpose(IF($C$2:$C$999=$A4,$A$2:$A$999,""))...)
but you must have many columns to the right since empty results still occupy columns!
Even if empty strings are handled to occupy no columns, there still remains the matter of unknown results-columns. So I propose usage of a VBA function (one I wrote quite a while ago) to Concatenate valid results in one cell (with any delimiter you like).
Final proposed formula in D3: =MyConCat("-";IF($C$2:$C$999=$A2;$A$2:$A$999;"")) +ctrl+shift+enter
and code for MyConCat (to place in a module in VBA code area):
' MyConCat
'
' Very simple By Apostolos Goulandris
Function MyConCat(myDelimiter As String, Avar) As String
Dim b As Variant, Dum As String
If IsMissing(myDelimiter) Then myDelimiter = ""
For Each b In Avar
Dum = IIf(Len(b) > 0, Dum & myDelimiter & b, Dum)
Next
MyConCat = IIf(Len(myDelimiter) > 0, Mid(Dum, Len(myDelimiter) + 1, Len(Dum)), Dum)
End Function
