Search a value in a column-B and return the values from column-A

[i200yrs]

Hello Excel Experts...need some help...see table below:
A B
1 Apple
2 Ball
3 Apple
4 Cat
5 Dog
I want an excel macro like if the user key-in "Apple" and click search will return values from column-A and list it in Column-C.
Like below result:
C
1
3

Hoping for usual supports...thanks

 

[Yongle]

Here is my amended VBA solution which returns all results

Right click on sheet tab\ select View Code \ paste this code into the new window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim C1 As Range, B As Range, result As Range, r As Long, addr As String
    Set C1 = Range("C1")
    Set B = Range("B1", Range("B" & Rows.Count).End(xlUp))
    r = 2
    If Not Intersect(Target, C1) Is Nothing Then
        Range("C:C").ClearContents
        C1 = InputBox("Enter string", "Text to find")
        Set result = B.Find(C1, LookIn:=xlValues)
        If Not result Is Nothing Then
            addr = result.Address
            Do
                Cells(r, "C") = result.Offset(, -1)
                r = r + 1
                Set result = B.FindNext(result)
            Loop While Not result.Address = addr
        End If
    Range("C2").Activate
    End If
End Sub

 

[i200yrs]

Have tried to fix...but i wondering why the number "1" doesn't show up...please help...thanks

 

[i200yrs]

Have tried to fix...but i wondering why the number "1" doesn't show up...please help...thanks

 

[i200yrs]

Have tried to fix...but i wondering why the number "1" doesn't show up...please help...thanks

 

[i200yrs]

Sorry for so many repeated message...my browser slowing..thought it was not published...sorry

 

[Yongle]

Did you try the VBA solution in post#11?

 

[i200yrs]

Did you try the VBA solution in post#11?

oh it works...perfect...thanks thanks and many thanks

 

[Tom.Jones]

I placed this formula in C2 but doesn't work...sorry am not good in excel...
=IF(C1=IFERROR(INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5="Apple",ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:C1))),"")

You not use my formula (where in my formula you see =IF(C1=IFERROR.....

Copy this formula and paste in C1 (Remember CTRL+SHIFT+ENTER)
=IFERROR(INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5="Apple",ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:C1))),"")

 

[Tom.Jones]

Use this array formula:

=IF($C$1="","",IFERROR(INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5="Apple",ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:C1))),"")

 

[Tom.Jones]

Use this array formula:

=IF($C$1="","",IFERROR(INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5="Apple",ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:C1))),"")

must be in C2:
=IF($C$1="","",IFERROR(INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5=$C$1,ROW($A$1:$A$5)-ROW($A$1)+1),ROWS($C$1:C1))),"")