I want to build a simple word translator which may look like this:

  • COLUMN A: I have a list of words, each on a row. (1:Airplane, 2:Car, 3:Cat, 4:Dog etc..)
  • COLUMN B: I have a list of the same words in another language, each on a row. (1:Aereoplano, 2: Macchina, 3:Gatto, 4:Cane etc..)
  • Then I have two cells. In the first one, I can type any word. The second cell is the formula that I want to create. The formula should:

    • check if "my word"(the word that I type) is in the list of column A
    • if it exists, it should return its adjacent word of the second column
    • If no words match, the formula should not return anything.

I'm struggling with finding the correct functions to accomplish this, any pointers are welcome.

UPDATE I finally found the solution. Best solution for me is
=IFERROR(VLOOKUP(B7;'Table 1-1'::B4:D53;3;FALSE);0)
REALLY thanks for your help.

  • 1
    Welcome to Ask Different! According to How to Ask in the help center, questions should include what you've already done to solve the problem yourself. Can you edit your question to include the ways you tried to solve this problem? This prevents us from suggesting solutions you've already tried. – fsb May 7 '17 at 15:07
  • @fsb If somebody doesn't know about VLOOKUP and friends, even the first step might be difficult. I wouldn't expect an answer to give a full solution but at least some indications on what to look for. – nohillside May 7 '17 at 15:17
  • After some researches, I found that the simple LOOKUP formula works perfectly for the purpose. Thanks for the help. – Mario May 7 '17 at 16:40
  • 1
    @patrix I didn't see any indication that the OP made an attempt to find the solution. I know it's difficult to get the idea of VLOOKUP across in this type of Q&A setting (it's best learned via tutorial). I was trying to avoid a 'try this...' and then 'try this...' back-and-forth and was encouraging the OP to search for solutions first and then let us know what help is needed. – fsb May 7 '17 at 17:55
  • 1
    If you got a solution, great. But please post it as an answer below (and upvote/accept it) so people will have it easier to find the solution in the future. – nohillside May 7 '17 at 19:13

This answer to a related question explains how to use VLOOKUP in combination with IFERROR.

If VLOOKUP cannot find the exact match, then it throws an error. To catch this error, wrapping the formula in the IFERROR function allows the author to supply a default (in the OP's case, an empty string "") to display in the event of no match.

The following example uses a third table to display the translation. That table is then locked so as to protect the formula from being inadvertently overwritten. The words NOT FOUND are used here to show the error event.

If match is found:

VLOOKUP no match

No match:


From the documentation:

VLOOKUP(search-for, columns-range, return-column, close-match)

search-for: The value to find. search-value can contain any value.

columns-range: A collection of cells. columns-range must contain a reference to a single range of cells, which may contain any values.

return-column: A number value that specifies the relative column number of the cell from which to return the value. The leftmost column in the collection is column 1.

close-match: An optional modal value that determines whether an exact match is required.

close match (TRUE, 1, or omitted): If there’s no exact match, select the row with the largest left-column value that is less than or equal to the search value. If you use close match, you can’t use wildcards in search-for.

exact match (FALSE or 0): If there’s no exact match, returns an error. If you use exact match, you can use wildcards in search-for. You can use the wildcard ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard.

  • Ah bjbk! I woke up this morning and I wanted to publish a complete explanation of the process... and you made this gem!! Thanks. That's exactly what I did and you represented in the clearest way. Thanks! – Mario May 9 '17 at 6:18
  • Glad it helped! If it solves the problem pleas mark as answered for the benefit of others. Cheers! – bjbk May 9 '17 at 13:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .