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
 

Some videos you may like

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

Yongle

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

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
338
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@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
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Tom.Jones - thanks for the catch :oops:
 

i200yrs

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

ADVERTISEMENT

@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
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Sep 20, 2011
Messages
338
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Dec 18, 2019
Messages
22
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

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