Match one cell for another AND give results

[krayziez]

A1:
cat

A2: cat1 B2: feline1
A3: dog1 B3: canine1
A4: cat2 B4: feline2
A5: dog2 B5: canine2
A6: dog3 B6: canine3

A7 should be formula to return all values on column B that matches th
range of A2:A6 given A1 value

Right now, all I can do is do a match for all items containing "cat" o
A1:
=ISNUMBER(MATCH("*"&A1&"*",A2:A6,0))

Is there a way of returning the value of B column for the correspondin
A column (same row)?

Ultimately, I'd like cell A7 to look like this:
feline1, feline2

I hope I can do this without programming. I'm sure I can with a comb
of match and vlookup

 

[krayziez]

I'm actually able to do this:
=VLOOKUP("*"&A1&"*",A2:B6,2,FALSE)

but it only returns to me the first one that matches and not all:
feline1

 

[Herbert Seidenberg]

Here is a way using Advanced Filter
BinA BinB
cat1 fel1
dog1 can1
cat2 fel2
dog2 can2
cat3 fel3
dog3 can3

Pick
cat

Criteria
Test
FALSE

fel1
fel2
fel3

Name BinB, Pick, Criteria with
Insert > Name > Create > Top Row
Into the cell that says FALSE, enter
=ISNUMBER(SEARCH(Pick,OFFSET(BinB,,-1)))
Data > Filter > Advanced Filter > Copy to another location
List Range: BinB and its header
Criteria Range: Criteria (2 cells)
Copy to: A16
Concatenate, Transpose results as desired.

 

[krayziez]

What did you mean by these statements:

Name BinB, Pick, Criteria with
Insert > Name > Create > Top Row

Can you explain this further? Also, can you do it so that the results
is just in one cell? Like: " fel1, fel2, fel3 " . I have to do this
for 1000+ cells

 

[Herbert Seidenberg]

To give the name <Pick> to cell A10,
select A9, the header with the arbitrary name <Pick>,
and A10, which contains either dog or cat, and
from the tool bar, step through this sequence of options:
Insert > Name > Create > Top Row
If successful, the name <Pick> will appear in the upper left
corner of the screen when you select cell A10.
Otherwise, substitute my arbitrary names with these suggested ranges:
BinB $B$2:$B$7
Pick $A$10
Criteria $A$13:$A$14
Advanced Filter > List Range $B$1:$B$7

Assuming the results appear at A17,
to concatenate them, enter at B17
=A17
and enter at B18
=CONCATENATE(B17,",",A18)
and double click the fill handle.