can you do searchable drop lists on excel 2021?

Hi,

I found the formula on a you-tube video

here are links

https://www.youtube.com/watch?v=e2-uc3nOKlE&t=10s

https://www.youtube.com/watch?v=8gqodEiDoJ8

and these are his steps

Steps

1 Type few characters as a search term in A1

2 Create a "SEARCH" function in E2

	=SEARCH($A$1,F2)									

	This function returns either a number or a Value error					

3 Put the SEARCH function in an "ISNUMBER" function

	=ISNUMBER(SEARCH($A$1,F2))							

	Converts the result to "TRUE" & "FALSE"							

4 Put the above functions into an "IF" function

	=IF(ISNUMBER(SEARCH($A$1,F2)),1,0)							

	Converts the result to ones & Zeros & "FALSE"						

	a "ONE" means the keyword exists							

5 Replace the "1" by a MAX function to increment each occurrence

	=IF(ISNUMBER(SEARCH($A$1,F2)),MAX($E$1:E1)+1,0)					

	Lock the first reference for the MAX							

6 Create a "VLOOKUP" function to extract all Occurences

	The "ROWS" function creates incremental numbers					

	Use "ROWS" as a Lookup value inside the "VLOOKUP"					

	Lock the First reference for the Rows function						

	=VLOOKUP(Rows($H$2:H2),$E$2:$F:$135,2,FALSE)						

	Drag the VLOOKUP function down to almost 50 cells					

7 Eliminate errors the VLOOKUP function might return by including it in an ISERROR

	=ISERROR(VLOOKUP(Rows($H$2:H2),$E$2:$F:$135,2,FALSE),"")				

	If there is an error a blank "" will be returned						

8 Count the number of results returned by the VLOOKUP function

	We'll use a COUNTIF function to count Text						

	We need to use some wild cards (Replacement characters)					

	The wild cards I am using are								

	?	Replaces any one single character						

	\*	Replaces any number of characters						

	=COUNTIF(H:H,"?\*")								

9 The OFFSET function store a reference to the range of results of the VLOOKUP 

	OFFSET requires 5 arguments, the first 3 describe the Starting point				

	the last 2 describe how far down and to the right you expand the range			

	For the number of Rows down, I will use my COUNTIF function				

	Create the function in cell in G2							

	It will return only the first result of the VLOOKUP function					

	=OFFSET($H$2,0,0,COUNTIF(H:H,"?\*"),1)							

	Copy the function to use it in the next step						

10 Create a Defined Name: Formulas Tab --> Define Name

	Name it "MyList"									

	Paste the copied OFFSET function in the "Refers To" box					

	Hit OK										

11 Create the drop list on the Data Tab --> Data Validation

	Shortcut: ALT, D, L									

	Allow: List									

	Source: "MYList" (You can hit F3 for selecting the name					

	Hit OK										

Test your Drop List by typing different keywords

To be able to copy down  column 

You need to change  This  $A$1 

=IF(ISNUMBER(SEARCH($A$1,F2)),MAX($E$1:E1)+1,0)

to  (INDIRECT(CELL("address"))

=IF(ISNUMBER(SEARCH(INDIRECT(CELL("address")),F2)),MAX($E$1:E1,0)+1,0)

then select the cells and re do the data validation

hope this helps

Please share the formula. I'm having the same problem.

Hi Femi,

Thank you for that information I already knew that.

not what I wanted but thanks

I had to put a formular in to get it work.

all good now :)

thanks so much for time!

Hi, Rose . Thank you for coming to the forum. My name is Femi. It is worth noting that this is a user community, and we are all users here, much like yourself. While we strive to assist and share insights, we are not Microsoft directly.

I am sorry that you can't do a searchable droplist on Excel As a user, I understand how frustrating that can be. Rest assured I will do my best to provide you with a solution.

I take it you're using a Windows device to access Office.

According to the article on ( https://support.microsoft.com/office/7693307a-59ef-400a-b769-c5402dce407b ) you can do a searchable droplist following these steps:

"In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, then you can quickly convert your list to a table by selecting any cell in the range, and pressing Ctrl+T.

Select the cell in the worksheet where you want the drop-down list.

Go to the Data tab on the Ribbon, and then Data Validation.

On the Settings tab, in the Allow box, select List.

Select in the Source box, then select your list range. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:

If it’s OK for people to leave the cell empty, check the Ignore blank box.

Check the In-cell dropdown box.

Select the Input Message tab. If you want a message to pop up when the cell is selected, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.

Select the Error Alert tab.

If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you don’t want a message to show up, clear the check box.

Not sure which option to pick in the Style box?

(To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, select Information or Warning. Information will show a message with this icon Message shows Information icon but doesn't stop people from choosing from the drop-down list and Warning will show a message with this icon Message shows Warning icon but doesn't stop people from choosing from the drop-down list.)

To stop people from entering data that isn’t in the drop-down list, select Stop.

Note: If you don't add a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

I hope this helps, let me know if this contradicts what you need. I remain helpful in answering any more of your questions.

Best Regards,

Femi.

Hi again, Rose. I am glad you were able to resolve the issue.

Could you please share in details the formula you used? This would help other users facing the same problem.

Thanks Femi