Need to search multiple cell value and fill it all into another cell

[andre304]

Hi everyone,
I try to find a possible causes value in from another sheet and put all the value that match into another sheet.
Attachment 822989
I want to make the "possible cause" value come from other sheet and fill the column as it need.
For example, if i choose equipment "centrifugal", and problem "Pump overheat", in "possible cause" column, the value is as shown below.
Exm.png
And this works with the other sheet too.
What is the formula?
Guide for Suffering.xlsx

[bebo021999]

I'd offer a VBA solution, to trigger change in cell B3, C3 sheet Guide
Pick value from dropdown, and see a list in column D generated

How to use:
Right click tab's name. View Code, paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&, j&, k&, prob As String
Dim wsname As String, rng As String, ceR, res(1 To 1000, 1 To 1)
If Intersect(Target, Range("B3:C3")) Is Nothing Then Exit Sub
Select Case [B3].Value
    Case "Nemo"
        wsname = "Nemo Guide": rng = "B3:M34"
    Case "Teikoku"
        wsname = "Teikoku Guide": rng = "B4:S30"
    Case "Centrifugal"
        wsname = "Centrifugal Pump Guide": rng = "B3:P27"
End Select
prob = Range("C3").Value
With Sheets(wsname)
    ceR = .Range(rng).Value
    If WorksheetFunction.CountIf(.Range(rng), prob) = 0 Then
        Range("C3").Select
        Exit Sub
    End If
    For j = 1 To UBound(ceR, 2)
        If ceR(1, j) = prob Then
            For i = 3 To UBound(ceR)
                If ceR(i, j) <> "" Then
                    k = k + 1: res(k, 1) = ceR(i, UBound(ceR, 2))
                End If
            Next
        End If
    Next
End With
Range("D3:D10000").ClearContents
Range("D3").Resize(k, 1).Value = res
End Sub 

Save as file as .xlsm

[andre304]

Thanks for the solution. But can you create with excel formula?
I cant run vba and macro in my PC.

[JeteMc]

Here is a formula based proposal:

=IF(B$3="Centrifugal",IFERROR(INDEX('Centrifugal Pump Guide'!P$5:P$27,AGGREGATE(15,6,(ROW('Centrifugal Pump Guide'!P$5:P$27)-ROW('Centrifugal Pump Guide'!P$4))/('Centrifugal Pump Guide'!B$3:O$3=C$3)/('Centrifugal Pump Guide'!B$5:O$27="x"),ROWS(D$3:D3))),""),IF(B$3="Nemo",IFERROR(INDEX('Nemo Guide'!M$5:M$34,AGGREGATE(15,6,(ROW('Nemo Guide'!M$5:R$34)-ROW('Nemo Guide'!M$4))/('Nemo Guide'!B$3:L$3=C$3)/('Nemo Guide'!B$5:L$34="x"),ROWS(D$3:D3))),""),IF(B$3="Teikoku",IFERROR(INDEX('Teikoku Guide'!S$6:S$30,AGGREGATE(15,6,(ROW('Teikoku Guide'!S$6:S$30)-ROW('Teikoku Guide'!S$5))/('Teikoku Guide'!B$4:R$4=C$3)/('Teikoku Guide'!B$6:R$30<>""),ROWS(D$3:D3))),""))))

After pasting the formula into cell D3 drag the fill handle down to cell D19.
If you need to see a copy of the file with the formula applied, then please attach a file without protected sheets.
Let us know if you have any questions.