How to Create a Text Search Box

[BB38]

Hi -

In my spreadsheet, I'm trying to create a search box in cell E5 - that searches cells B10-B242 (the data in my table). The data is a set of unique names.

Ideally I want the user to type the name they are looking for in the search box, and then receive back in another box the cell number if the name exists in the database. If it doesn't I want it to say "not found". For names that it did find , i'd also like that name to be highlighted in its cell.

does that make sense? can anyone help?

 

[severynm]

Something like:

=IFERROR(MATCH(nametofind,rangetosearch,0),"not found")

In your case, nametofind is the value in E5, and rangetosearch is B10-B242.

If a match is found, the Match returns the index of the array you used to search (i.e. if a match is in the 4th cell of the array, Match will return 4). If there's no match, Match returns an error, hence the error handling.

To highlight the cell, you might be able to either

1. Add a helper column with something like

   =MATCH($E$5,$B10,0)

   , and then a conditional formatting rule to highlight if not an error error (the cell is not a match)

   NOT(ISERROR(cell))

2. Or you could try a VBA solution to select and/or highlight the cell.

 

[BB38]

Thank you. I am a relatively average excel user so...will try to figure these out. I appreciate it!

 

[BB38]

Something like:

=IFERROR(MATCH(nametofind,rangetosearch,0),"not found")

In your case, nametofind is the value in E5, and rangetosearch is B10-B242.

If a match is found, the Match returns the index of the array you used to search (i.e. if a match is in the 4th cell of the array, Match will return 4). If there's no match, Match returns an error, hence the error handling.

To highlight the cell, you might be able to either

1. Add a helper column with something like

   =MATCH($E$5,$B10,0)

   , and then a conditional formatting rule to highlight if not an error error (the cell is not a match)

   NOT(ISERROR(cell))

2. Or you could try a VBA solution to select and/or highlight the cell.

I am making progress here but it's not quite fully working:

1. How can I make the query not an "exact" match. Meaning if the name was say... "Walrus Rugs" but just typing "Wal" should show me the possible matches. I realize this is different from original query.
2. When the query returns me the row the name is in, it's giving me a number that is off by 10. How can I correct that?

This is for a new job and thank you for this help it is invaluable!

 

[severynm]

1. To get a fuzzy match, you can append a wildcard to the end, so it will return the first result that starts with the entered string. Your formula would turn into something like:

   =MATCH($E$5 & "*",$B10,0)

   Showing all possible matches is slightly more complex where it would likely require you to filter the table in some way. Note you also can't use this to search for something within a name - only for results that start with your query. I see you're using O365 which is nice because it gives you some more options here. If you want anything more advanced, maybe look at FILTER or XLOOKUP.
2. I don't know the format of your table and data but based on what you've mentioned, your range starts at the 10th row (B10), so that's the first place I'd look. I don't know if you modified any formulas, but if you're consistently off by 10, you can always just add 10 to the Match result to get the proper row.

 

[BB38]

Something like:

=IFERROR(MATCH(nametofind,rangetosearch,0),"not found")

In your case, nametofind is the value in E5, and rangetosearch is B10-B242.

If a match is found, the Match returns the index of the array you used to search (i.e. if a match is in the 4th cell of the array, Match will return 4). If there's no match, Match returns an error, hence the error handling.

To highlight the cell, you might be able to either

1. Add a helper column with something like

   =MATCH($E$5,$B10,0)

   , and then a conditional formatting rule to highlight if not an error error (the cell is not a match)

   NOT(ISERROR(cell))

2. Or you could try a VBA solution to select and/or highlight the cell.

I got the original goal completed. Can you expand on the "helper" column. I create another column but what do I change about the data. Forgive me for being dense. I haven't done a helper column and was unsure why I needed one to then try the highlighting solution.

Thanks again for all this help.

Brad

 

[BB38]

Something like:

=IFERROR(MATCH(nametofind,rangetosearch,0),"not found")

In your case, nametofind is the value in E5, and rangetosearch is B10-B242.

If a match is found, the Match returns the index of the array you used to search (i.e. if a match is in the 4th cell of the array, Match will return 4). If there's no match, Match returns an error, hence the error handling.

To highlight the cell, you might be able to either

1. Add a helper column with something like

   =MATCH($E$5,$B10,0)

   , and then a conditional formatting rule to highlight if not an error error (the cell is not a match)

   NOT(ISERROR(cell))

2. Or you could try a VBA solution to select and/or highlight the cell.

one other question: if I already have a function for another item in VBA (module 1) - how do I create/add another function? do I add it into the same module? I guess I don't understand how to put the formula you gave me into VBA (helper column with formula)....

 

[severynm]

I got the original goal completed. Can you expand on the "helper" column. I create another column but what do I change about the data. Forgive me for being dense. I haven't done a helper column and was unsure why I needed one to then try the highlighting solution.

Thanks again for all this help.

Brad

No problem. The purpose of helper columns is to simplify formulas, you could get away without it, but it makes formulas more complex. The helper column contains the formula =MATCH($E$5,$B10&"*",0). This will return a number for all matches and #N/A for all non matches. The column can be hidden if you so choose. Then, use conditional formatting to reference this column and highlight all cells that are no errors =IF(NOT(ISERROR(helpercolumn)),1,0). The conditional formatting is applied to B10-B242.

If you didn't want to use a helper column, your conditional formatting formula would be something like =IF(NOT(ISERROR(MATCH($E$5,$B10&"*",0))),1,0). Disadvantage is a more complex formula but advantage is you don't need a helper column for the formula.

one other question: if I already have a function for another item in VBA (module 1) - how do I create/add another function? do I add it into the same module? I guess I don't understand how to put the formula you gave me into VBA (helper column with formula)....

In general, yes you do. You can add as many things as you want to a module. Sorry if I was confusing above, but this solution uses entirely excel formulas + conditional formatting, so you don't need to add any VBA.

See the spreadsheet below with some sample data.

Book1
	A	B	C	D	E	F
1		Rep	[helper column]
2		Jones	#N/A		nameToFind	result
3		Kivell	#N/A		sor	5
4		Jardine	#N/A
5		Gill	#N/A
6		Sorvino	1
7		Jones	#N/A
8		Andrews	#N/A
9		Jardine	#N/A
10		Thompson	#N/A
11		Jones	#N/A
12		Morgan	#N/A
13		Howard	#N/A
14		Parent	#N/A
15		Jones	#N/A
16		Smith	#N/A
Sheet1

Cell Formulas
Range	Formula
F3	F3	=IFERROR(MATCH($E$3&"*",B2:B16,0),"not found")
C2:C16	C2	=MATCH($E$3&"*",B2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B16	Expression	=IF(NOT(ISERROR(C2)),1,0)	text	NO