Autocomplete search word based on Initial first/random word

Hi All,

I've just come to a solution for this, and this works pretty well... I'll explained it to you.

Current situation :
1. user want to search between database, he/she doesn't remember the exact word by word contained.

2. each data is unique and no duplicate
ex : Indonesia, Indonesia Jakarta, Indonesia Surabaya, etc

3. user will type random letter just to guess this unique database,
ex : for searching data "Indonesia Jakarta", He'll type "ijkt", or "ijak",or "indojaka", and the possibilities are unlimited



Solution : I've come up with

Scenario 1, if it's RANDOM word / letter ex : "ijkt", "ijak", or "indojaka"
conventional dynamic search won't do you any good, you need to breakdown each search letter by letter, and fulfill this condition.

i use, for each n-th letter that detected on the cell you gonna search
=IF(ISNUMBER(SEARCH(MID($B$4,E$2,1),$Q$4:$Q$20)),MAX(E$3:E3)+1,0)

and this function, to detect, if each of those letter contained in certain word? if its true, it'll show you the search result
=IF(IF(AND(E4>0,F4>0,G4>0,H4>0,I4>0,J4>0,K4>0,L4>0,M4>0,N4>0)=TRUE,1,0)=1,MAX($P$3:P3)+1,0)

rumus1.JPG


Scenario 2, if it's PART of the word ex "jakarta", "jakar", "indonesia", "indo"
conventional dynamic search will be okay (I adapt it from youtube tutorial by my e-lesson https://youtu.be/Jr34riKGveg )

quoted from the tutorial, the function will result part of word search
=IF(ISNUMBER(SEARCH($B$4,$Q$4:$Q$20)),MAX(O$3:O3)+1,0)

rumus2.JPG


Scenario 3, how do we know, if the person type random word or part of the word?



there'll be 2 suggestions

1st suggestion Part of word
partofword.JPG

2nd suggestion Random word
randomword.JPG

as the 3rd suggestion will prioritize if the 1st suggestion detected, it'll come first, if not it'll be the 2nd suggestion, i just use simple function
=IF(LEN(S4)>0,S4,T4)


Have good day,
wish you luck

Can you please share the above template it would help me - Please send me the finished or the final file. I really liked the logic

Hi Syedshahab,

My post no #2, the solution was almost perfect, yet 2 out of 10 keywords results missmatch with what the user wants. Then my colleague revised & give advise, to use different approach.
the new idea was, to evaluate & locate each word from front to end continuously, and rank it based on the most match counted.

here's the final (in the attachment). it gives higher accuracy, but until now we still not find miss match keyword.

thanks