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

i200yrs

New Member
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

i200yrs

New Member
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
Have tried to fix...but i wondering why the number "1" doesn't show up...please help...thanks

Capture.JPG
 

i200yrs

New Member
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
Have tried to fix...but i wondering why the number "1" doesn't show up...please help...thanks
 

i200yrs

New Member
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
Have tried to fix...but i wondering why the number "1" doesn't show up...please help...thanks
 

Attachments

  • Capture.JPG
    Capture.JPG
    45.7 KB · Views: 3

i200yrs

New Member
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Did you try the VBA solution in post#11?
 

i200yrs

New Member
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows

ADVERTISEMENT

Did you try the VBA solution in post#11?
oh it works...perfect...thanks thanks and many thanks
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
337
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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))),"")
 
Last edited:

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
337
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Sep 20, 2011
Messages
337
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,180
Messages
5,623,216
Members
415,957
Latest member
Newguy1924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top