Return Cell references of specific text in range

[glynn1969]

Hello i will try to keep my query simple.

I have a tab called Data and in column A i have 1000's of surnames (in no particular order).

i would like to be able to enter a search text in cell d2 and then next my search box (so in e2 and across - to then return ALL the cell references that contain that surname - eg it may show A12 A432 A634 A6443

i can do this to return the first occurrence of the surname but can't seem to get all occurrences displayed as a list.

Is this possible.

Thank you in advance

 

[glynn1969]

Perhaps:

Book1
	A	B	C	D	E	F	G	H
1	Names			Search
2	Apple			Apple	A2	A9	A10	A11
3	Bonnie
4	Carol
5	Dave
6	Egg
7	Friend
8	Ghee
9	Apple
10	Apple
11	Apple
12	Bonnie
13	Bonnie
14	Egg
15	Egg
16	Egg
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TOROW("A"&FILTER(ROW(A2:A16),A2:A16=D2))
Dynamic array formulas.

Maybe something like this?

Book1
	A	B	C	D	E	F	G	H
1	Names			Search
2	Apple			Apple	$A$2	$A$9	$A$10	$A$11
3	Bonnie
4	Carol
5	Dave
6	Egg
7	Friend
8	Ghee
9	Apple
10	Apple
11	Apple
12	Bonnie
13	Bonnie
14	Egg
15	Egg
16	Egg
17
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TOROW(FILTER(BYROW(A2:A16,LAMBDA(r,CELL("address",r))),A2:A16=D2))
Dynamic array formulas.

Hello that works great, I just winder if the formulae could be adapted for a partial match.

I did try amending D2 to be "*"&D2&"*" but this didn't work for me. Could you advise please?

 

[glynn1969]

Perhaps:

Book1
	A	B	C	D	E	F	G	H
1	Names			Search
2	Apple			Apple	A2	A9	A10	A11
3	Bonnie
4	Carol
5	Dave
6	Egg
7	Friend
8	Ghee
9	Apple
10	Apple
11	Apple
12	Bonnie
13	Bonnie
14	Egg
15	Egg
16	Egg
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TOROW("A"&FILTER(ROW(A2:A16),A2:A16=D2))
Dynamic array formulas.

Hello that works great, I just wonder if the formulae could be adapted for a partial match.

I did try amending D2 to be "*"&D2&"*" but this didn't work for me. Could you advise please?

 

[Cubist]

Try

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Names			Search
2	Apple			Apple	A2	A3	A4	A8	A9	A10	A15
3	AppleBee
4	AppleSauce
5	Dave
6	Egg
7	Friend
8	Macbook Apple
9	Apple
10	AppleJuice
11	Bonnie
12	Bonnie
13	Egg
14	Egg
15	The Greateset Apple Tree
16
Sheet10

Cell Formulas
Range	Formula
E2:K2	E2	=TOROW(CHAR(COLUMN(A2:A15)+64)&FILTER(ROW(A2:A15),ISNUMBER(SEARCH(D2,A2:A15))))
Dynamic array formulas.

 

[Scott Huish]

It's still only Column A.

Book1
	A	B	C	D	E	F	G	H
1	Names			Search
2	Apple			Apple	A2	A9	A10	A11
3	Bonnie
4	Carol
5	Dave
6	Egg
7	Friend
8	Ghee
9	Apple Pie
10	Apple
11	Apple
12	Bonnie
13	Bonnie
14	Egg
15	Egg
16	Egg
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TOROW("A"&FILTER(ROW(A2:A16),ISNUMBER(SEARCH(D2,A2:A16))))
Dynamic array formulas.

 

[glynn1969]

Try

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Names			Search
2	Apple			Apple	A2	A3	A4	A8	A9	A10	A15
3	AppleBee
4	AppleSauce
5	Dave
6	Egg
7	Friend
8	Macbook Apple
9	Apple
10	AppleJuice
11	Bonnie
12	Bonnie
13	Egg
14	Egg
15	The Greateset Apple Tree
16
Sheet10

Cell Formulas
Range	Formula
E2:K2	E2	=TOROW(CHAR(COLUMN(A2:A15)+64)&FILTER(ROW(A2:A15),ISNUMBER(SEARCH(D2,A2:A15))))
Dynamic array formulas.

This is absolutely fantastic - thank you so much

 

[glynn1969]

It's still only Column A.

Book1
	A	B	C	D	E	F	G	H
1	Names			Search
2	Apple			Apple	A2	A9	A10	A11
3	Bonnie
4	Carol
5	Dave
6	Egg
7	Friend
8	Ghee
9	Apple Pie
10	Apple
11	Apple
12	Bonnie
13	Bonnie
14	Egg
15	Egg
16	Egg
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TOROW("A"&FILTER(ROW(A2:A16),ISNUMBER(SEARCH(D2,A2:A16))))
Dynamic array formulas.

This is absolutely fantastic - thank you so much

 

[glynn1969]

This is absolutely fantastic - thank you so much

Going to be very cheeky and ask - is there a formula or VBA that might be able to search through a folder containing any number of workbooks in a similar form to the one in this example. So searching for a particular text (partial match) in column A and so returning the name of the workbook where the text is found (and the tab it is on if possible if there are multiple tabs containing the text) - but to do this without me actually manually opening each workbook.

 

[Cubist]

It's doable with VBA but that's beyond the scope of this thread. Please start a new thread.

PS: You don't have to repeat your replies. We can see both.

 

[glynn1969]

It's doable with VBA but that's beyond the scope of this thread. Please start a new thread.

PS: You don't have to repeat your replies. We can see both.

Will do thank you both so much for your help...really saved me a lot of time in work

 

[smozgur]

This is absolutely fantastic - thank you so much

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.