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

i200yrs

New Member
Joined
Dec 18, 2019
Messages
43
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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)
    If Target.Address <> "$C$1" Then Exit Sub
    Target = InputBox("Look for ?", "Enter some text")
    On Error Resume Next
    With Target.Offset(1)
        .Value = "not found"
        .Value = WorksheetFunction.Match(Target, Range("B:B"), 0)
        .Activate
    End With
    On Error GoTo 0
End Sub
 
Upvote 0
without any VBA

Place this formula in C2
=IF(C1="","",IFERROR(INDEX(A:A,MATCH(C1,B:B,0)),"Not found"))

enter search value in C1
 
Upvote 0
@Yongle,

OP want:
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

Both your solution give only "1" (must be also "3")

This array formula should do:
In 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:C2))),"") then drag down.
 
Last edited:
Upvote 0
@Yongle,

OP want:


Both your solution give only "1" (must be also "3")

This array formula should do:
In 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:C2))),"") then drag down.

Thanks @Yongle and @Tom.Jones.... by the way, if i place the formula in C1,,,where should i place the search value? thanks
 
Upvote 0
@Yongle,

OP want:


Both your solution give only "1" (must be also "3")

This array formula should do:
In 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:C2))),"") then drag down.

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:C2))),"")
 
Upvote 0
@Tom.Jones said it is an ARRAY formula
an ARRAY formula is confirmed with {CTRL}{SHIFT}{ENTER} not the usual {ENTER}

If you do this correctly the cell formula looks like this with the formula inside curly brackets { }
{ =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:C2))),"") }
 
Upvote 0
This array formula should do:
In 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:C2))),"") then drag down.

After paste this formula in C1, press F2 (to edit formula) then press Ctrl + Shift + Enter
(press CTRL and SHIFT and keep presed then press ENTER)
 
Upvote 0
Got an error saying missing an open or close parenthesis :(
I pasted this formula into cell C2 --> =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:C2))),"")
and then pressed CTRL and SHIFT + ENTER
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top