0

I have a spreadsheet which is a dictionary mapping fantasy words to english definitions/words. I would like to be able to type the english word and get a list of matching fantasy words, when typing into a cell. How can I accomplish that in Google Spreadsheets, perhaps using an app script somehow? Is it possible?

For example, given this:

foo,device
bar,devious
baz,phone

If I search "dev" it should show "foo" and "bar", or if I search "ph" it shows "baz", in an autocomplete list. Is anything like this possible in Google Sheets? It would help so much in mapping English words to fantasy words, I could simply type the English meaning and get the fantasy word, without having to go back to the fantasy sheet, CMD+F find the English word, then copy/paste the corresponding fantasy word back into the other sheet.

1 Answer 1

2

You can use SEARCH as a FILTER condition. FILTER will treat the results of SEARCH as an array. Also SEARCH is not case sensitive.

Columns A and B are your fantasy words and their english definitions. Enter the search term in C1.

Wrap the SEARCH formula into an ARRAYFORMULA to see how/why this works: =ARRAYFORMULA(SEARCH(C1,B1:B3))

Add a descriptive error message if there's no match: =IFERROR(FILTER(A1:B3,SEARCH(C1,B1:B3)),"no match")

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.