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

#### i200yrs

##### New Member
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Yongle

##### Well-known Member
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 = WorksheetFunction.Match(Target, Range("B:B"), 0)
.Activate
End With
On Error GoTo 0
End Sub``````

#### Yongle

##### Well-known Member
without any VBA

Place this formula in C2

enter search value in C1

#### Tom.Jones

##### Active Member
@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:

#### Yongle

##### Well-known Member

@Tom.Jones - thanks for the catch

#### i200yrs

##### New Member
@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

#### i200yrs

##### New Member

@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))),"")

#### Yongle

##### Well-known Member
@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))),"") }

#### Tom.Jones

##### Active Member
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)

#### i200yrs

##### New Member
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

Replies
17
Views
165
Replies
3
Views
75
Replies
6
Views
77
Replies
1
Views
180
Replies
6
Views
131

1,127,485
Messages
5,625,042
Members
416,067
Latest member
newb08

### 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.

### Which adblocker are you using?

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

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