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

 

[RobOrBob]

You don't mean filter to show only those rows matching a value?

You mean actually return the cell references for a match such as $A$42 ? if so I assume you are using CELL with INDEX and MATCH (although I dont know if they are available in Excel 2016)?

 

[glynn1969]

You don't mean filter to show only those rows matching a value?

You mean actually return the cell references for a match such as $A$42 ? if so I assume you are using CELL with INDEX and MATCH (although I dont know if they are available in Excel 2016)?

hi i am using
=CELL("address",INDEX(Data!E:E,MATCH("*"&$D$1&"*",Data!E:E,0))) (formulae references are slightly different to my simplified query)


i am using office 365 - have updated my profile now to reflect

 

[Cubist]

Can you post a sample data? Just making sure I get your layout correctly.

 

[glynn1969]

Sorry I have just shut down my PC to go to sleep..in uk and its 01.51, replying on phone.

My list simplified list is essentially a list of names in column A...thousands long.

I want to enter a name in cell D2 of the same tab and then show in cells e2,f2,g2 etc all the cells my my search name ,do, is found.

Will try to post example in morning.

 

[Cubist]

Are the surnames in column A appear exactly like the search cell D2 or do they need to return similar results i.e. searching for "May" will return "May", "Mayhem", and "Maybe"?

 

[glynn1969]

Are the surnames in column A appear exactly like the search cell D2 or do they need to return similar results i.e. searching for "May" will return "May", "Mayhem", and "Maybe"?

Exact would be fine...I know my formula does partial, but a full list of exact matches would be great if it is possible

 

[Cubist]

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.

 

[Scott Huish]

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.

 

[Cubist]

@Scott Huish
I like your solution more than mine because it doesn't use CELL() which is volatile. I might add CHAR() & COLUMN() to make it more dynamic in case the columns shift. Not sure if you have a shorter way.

=TOROW(CHAR(COLUMN(A2:A16)+64)&FILTER(ROW(A2:A16),A2:A16=D2))