Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #693 Aug 13, 2012 www.energyefficientbuild.com.
user TraditionalPanda said: (http://productforums.google.com/forum/?zx=76albpq7qu02#!category-topic/docs/spreadsheets/KUZBzidZjhI%5B1-25%5D)
Search Column A for a word and display Column B for that row in Column D based on user input in Column C
I'm trying to type a word into column C and then have it perform a search on Column A and display the corresponding Column B entry, either one word at a time or concatenated.
user TraditionalPanda said: (http://productforums.google.com/forum/?zx=76albpq7qu02#!category-topic/docs/spreadsheets/KUZBzidZjhI%5B1-25%5D)
Search Column A for a word and display Column B for that row in Column D based on user input in Column C
I'm trying to type a word into column C and then have it perform a search on Column A and display the corresponding Column B entry, either one word at a time or concatenated.
It's a rudimentary translator for a language that does not exist in Google Translate. This is all text; columns are formatted as text only.
Example:
Column A Column B
journey s^`My5($
is `B£
half 9Cje
------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem
I found that some words like "i", "in", "the" are not recognized in the list with this formula unless I rename them to "*i*" "in*" "the*" and was wondering if there's a way to fix this. Also (and this isn't as important), if one word is not recognized, it negates the whole string to "not available" ... but this is great overall and a lot easier than what I was doing (using Excel search function and manually copying the word it corresponds to)
ReplyDeleteHi Chris Beeney:
ReplyDeleteI had no problem with "the" which I have since added to the illustration in my blog post. I don't know what the problem is with "i" and "in" ... I will check on these when I have time.
Good Luck With Your Project.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
example words that are not available:
ReplyDeletethe = @
in = `B5
i = ~B
it = `B1
is = `B£
you = hH`M
not = 5^1
to = 1^
fear = eF`C6 (I also have fearless that works)
who = o^ (I also have whom that works)
even = `Vr$5 (I also have evening that works)
column A is all lowercase and column B is case sensitive if that matters. Also I noticed that "of" didn't work until I typed "of the" and waited for it to resolve, then erased the "the"
Hi Chris:
ReplyDeleteI think I have things working now ... please check it out and let me know everything works for you now.
Cheers!
Yogi
The updated equation works like a charm in Google Docs. For anyone using this code, if it says NA, make sure you don't have any spaces after the word you are searching for.
ReplyDeleteI was wondering if you know why Excel 2010 states invalid function and then highlights "split"
Hi Chris:
ReplyDeleteSorry, I don't have access to Excel 2010 handy ... so I can't say about what Excel 2010 is reporting.
Cheers!
yogi