Multi-Step problem - text box input with output and a conditional formatting challenge

[BB38]

Hi -

I have a database with Column B being 500 or so unique names.

I have created a text box where I can input the name and the output will be the row in which the name is found. The formula I have used for this is: =IFERROR(MATCH(F8 & "*",Table2[Investor],0), "Not Found") +10

Now I want to add another feature:

I want the output to stay as it is but:

1. If there are multiple outputs for the same letters (for example, if there are 10 names that start with AD in column B) then I want the output to show me all the lines these are found on (right now the output can only show me one line number)

2. I want to have whatever the output is in #1 above - to then highlight the cells in Column B

Please let me know if I can expand further

BB

 

[mart37]

Maybe you can use this example:

Map1
	A	B	C	D	E	F	G	H
1		jan						9
2		piet						6
3		klaas						2
4		henk
5		gert
6		peter
7		mien
8		klaas				p
9		peter
10		krijn
Blad1

Cell Formulas
Range	Formula
H1:H10	H1	=IF(COUNTIF($B$1:$B$10,$F$8&"*")>=ROWS($B$1:B1),LARGE((ROW($B$1:$B$10))*(ISNUMBER(FIND($F$8,LEFT($B$1:$B$10,LEN($F$8)),1))),ROW()),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BB38]

Thank you for this possible solution.

If I may be so bold - I've got the sheet working well.

Cell F8 is Input by user, Cell G8 is row output

Formula i'm using is:

I just need the output to show more than just one row if more than one exists. e.g. instead of row 13 only, it ouputs that the name exists in row 13,15,17

=IFERROR(MATCH(F8 & "*",Table2[Investor],0), "Not Found")

anyone? Bueller? anyone?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2		Amber Valley		w	6, 7, 8, 19, 20
3		Birmingham		wa	7, 19
4		Bradford
5		Cannock Chase
6		Cherwell
7		Cornwall
8		Cotswold
9		Eden
10		Hambleton
11		Kirklees
12		Oldham
13		Pendle
14		Shropshire
15		Stockport
16		Stoke-on-Trent
17		Teignbridge
18		Torridge
19		Waverley
20		Wycombe
Main

Cell Formulas
Range	Formula
E2:E3	E2	=TEXTJOIN(", ",,FILTER(ROW($B$2:$B$20),ISNUMBER(SEARCH(D2,$B$2:$B$20))))

 

[BB38]

but do I simply substitute my existing formula with yours (adjusted for rows and column numbers)? It took me a long time to get the formula I showed working right so...just want to understand how to do this.

 

[Fluff]

but do I simply substitute my existing formula with yours (adjusted for rows and column numbers)?

Yes that's right.

 

[BB38]

thank you! that worked, and now seeing how you did this is helpful. Very creative!

 

[BB38]

Yes that's right.

one last issue - I want the search to just be for the letters i've typed at the beginning of the name. So if I was looking for Edward, when I type "E" it will show me all the ones that begin with E, and if I type "Ed" then it would show me all names with ED. This formula is giving me all names that share the letters regardless of placement. How would I adjust the formula?

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E
1
2		Amber Valley		w	19, 20
3		Birmingham		e	9
4		Bradford
5		Cannock Chase
6		Cherwell
7		Cornwall
8		Cotswold
9		Eden
10		Hambleton
11		Kirklees
12		Oldham
13		Pendle
14		Shropshire
15		Stockport
16		Stoke-on-Trent
17		Teignbridge
18		Torridge
19		Waverley
20		Wycombe
21
Main

Cell Formulas
Range	Formula
E2:E3	E2	=TEXTJOIN(", ",,FILTER(ROW($B$2:$B$20),LEFT($B$2:$B$20,LEN(D2))=D2))

 

[BB38]

okay so i'll admit when I plugged this in I thought "no way this works"

I will be looking at this formula all night to understand it.

I am bowing and muttering supplications of thanks as I leave the throne room