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

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:

PHP Code: 

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

Here is a formula based proposal:

Formula:

Please Login or Register  to view this content.

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.