0

I want to find a short solution to find the matching Korean term of column A in "English" from sheet "Korean." For now I have split column A and then used Vlookup for each value, but this means that I need to create so many columns. Is there a shorter way to get the values without splitting the column?

https://docs.google.com/spreadsheets/d/11OXMnEMd6M4zIKLEC-BD-8sXpC3gG2tykhMkfXtVDIc/edit?usp=sharing

1

2 Answers 2

1

Try an array formula that looks up all the words in a cell and joins them, like this:

=arrayformula( 
  iferror( 
    join( 
      " ", 
      iferror( 
        vlookup( 
          split(A2, " "), 
          Korean!A$2:B, 
          columns(Korean!A$2:B), 
          false 
        ), 
        "[[[" & googletranslate(split(A2, " "), "en", "ko") & "]]]"  
      ) 
    ) 
  ) 
)

When a translation cannot be found, the formula uses googletranslate() to show an automatic translation, wrapped in triple square brackets [[[ ]]].

0

This question is a duplicate of Multiple substitutions in a single text posted in webapps in July 2013. I have adapted the answer by @AdamL.

This formula has several distinct benefits:

  • the formula will evaluate all the cells in Column A of "English"

  • If there are unusual words in Column B of "Korean", then the formula will return that value. For example, Cell A5 of "English" contains the "word" 'GMOs'. This is included in the "Korean" sheet, and the value of "#GMO" is displayed.

  • If there are misspelled words in the "Korean", then the misspelled value is returned. For example, "Guatemla" (also in Cell A5 of "English")

On the other hand, there are seven cells in Column B of "Korean" that are blank. The formula will also return a blank for those lookups. This can be easily fixed by inserting some appropriate 'flag' in each of the blank cells so that the lookup will at least display the 'flag'.

Insert this formula in cell B2:

=ARRAYFORMULA(TRIM(SUBSTITUTE(TRANSPOSE(TRIM(QUERY(TRANSPOSE(IFERROR(VLOOKUP(SPLIT(A2:A84," "),Korean!A:B,2,0),SPLIT(A2:A84," "))),,9^9)))," :",":")))


Sample

screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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