Vlookup when multiple values in one cell

Question

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

Answer 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 [[[ ]]].

Answer 2

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