Searchable dropdown list

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hi, I have created a dynamic searchable drop down list with around 1600 names and it's working perfect except for one thing. I have 2 names in the list "Rajesh" and "Rajesh Shah". Only one name appears in the drop down list when I type "rajesh". What could be the problem,?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
What happens if you type Rajesh Shah? The drow down is going to return the matched text string.
 
Upvote 0
What happens if you type Rajesh Shah?
I can select Rajesh Shah but only Rajesh is not visible. If I type only R then both are visible, but I have to search for both in the long list of 100 R's. Is Is not only this all other names which have common beginning word say Fashion and Fashion New then too it is the same problem
 
Upvote 0
I can select Rajesh Shah but only Rajesh is not visible. If I type only R then both are visible, but I have to search for both in the long list of 100 R's. Is Is not only this all other names which have common beginning word say Fashion and Fashion New then too it is the same problem
That is how the software works, I suppose. I don't believe I can help you with this.
 
Upvote 0
That is how the software works, I suppose. I don't believe I can help you with this.
Thanks for the reply. Maybe someone out there will be there to find a solution and help me to solve it.?
 
Upvote 0
I've done something similar using table formatting, conditional formatting, and filter. It won't return a single answer but will narrow down the table.
Pick the cell for your search term partial or otherwise. Select cells to search, conditional format with new formula,
=ISNUMBER(SEARCH(searchtermcell,celltosearch))
Conditional formatting has to be setup carefully. If C3:C1000 is your name list and C1 was your searchtermcell it would look like this when all cells to search are highlighted
=ISNUMBER(SEARCH($C$1,$C3))
After entering search term, filter table by color.
 
Upvote 0
I've done something similar using table formatting, conditional formatting, and filter. It won't return a single answer but will narrow down the table.
Pick the cell for your search term partial or otherwise. Select cells to search, conditional format with new formula,
=ISNUMBER(SEARCH(searchtermcell,celltosearch))
Conditional formatting has to be setup carefully. If C3:C1000 is your name list and C1 was your searchtermcell it would look like this when all cells to search are highlighted
=ISNUMBER(SEARCH($C$1,$C3))
After entering search term, filter table by color.
This are the formulas in my cell. Please advice what to do and in which formula?
=IF(ISNUMBER(SEARCH(CELL("contents"),F3)),MAX($E$1:E1)+1,0)
=IFERROR(VLOOKUP(ROWS($H$2:H2),LedgerMasters,2,0),"")
=OFFSET(H2,,,COUNTIF($H$2:$H$1472,"?*"))

All the names with 2 words in the table, of which the first word is common, only one name appears in the drop-down list.

Say I have 2 names in the list – 1. Reliance and 2. Reliance New,

If I type R both the names are visible in the search list below. If I type Reliance only one name is visible i.e., Reliance New and Reliance is not visible.

As the list is too long searching a name from a single alphabet will be tedious.
 
Upvote 0
Maybe a "Helper" column that removes spaces? I think you're asking about changing something in the way the EXCEL engine works which isn't an option.
 
Upvote 0
All the names with the name Rajesh is not displayed in the helper column. I hope this image helps to solve this.
Untitled.png
 
Upvote 0
Try to change this formula:

=OFFSET(H2,,,COUNTIF($H$2:$H$1472,"?*"))
to this
=OFFSET(H2,,,COUNTIF(Sheet1!$H$2:$H$1472,"*?"),1)
Change Sheet1 with name of the sheet
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top