Is there a function in Google Sheets to return the string next to a string that I match from a column of strings?

Question

Okay, Sheet1!F:F is a list of words in English. The same word occurs multiple times and the sheet is organized in order of chapters with the words in question in order as they appear in the chapter. "G:G needs to be that word in Arabic. "H:H needs to be the definition in English. "I:I needs to be the definition in Arabic.

Sheet2!A:A has the word in English, B:B the word in Arabic, C:C the definition in English, D:D the definition in Arabic.

Is there a function that would allow me to find the word from Sheet1!F:F in Sheet2!A:A and return Sheet2!B:B in Sheet1!G:G?

Here's some snipits of an example sheet. Sheet1! Sheet2!

Answer 1

You want to find the "Word AR" Sheet1 column G in "Word AR" Sheet2 column B, in other word find the arabic word for the English word from another table.

Paste this formula in Sheet1 cell G2, and drag it down.

=IF(F2="",,INDEX(Sheet2!$B$2:$B,IFNA(MATCH(F2,Sheet2!$A$2:$A,0),"No Match")))

Breakdown:

1 - MATCH function to find the matching row in the range Sheet2!$A$2:$A with [search_type] set to 0 to finds the exact value when range is unsorted.

2 - INDEX gives back a cell's content from a range when given a row and column, our reference is Sheet2!$B$2:$B we set the [column] as 1 or left it blank in case of a single column and pass the result of MATCH function as [row].

3 - handel N/A error with IFNA function and set [value_if_na_error] to "No Match".

4 - IF function IF(F2="",,[value_if_false] To calculate only when the cells of F columns are not blank.

hope that answers your question.

Answer 2

One option would be to use a VLOOKUP formula. For example:

=ifna(arrayformula(vlookup(A2:A, Sheet2!A2:D, 2,0)))

Sheet1:

Sheet2:

This formula can be adjusted to fit your needs:

=ifna(arrayformula(vlookup(F3:F, Sheet2!A2:D, 2,0)))
This can be placed in cell G3 of your Sheet1 and it will auto fill down the column. Repeat this for the next 3 columns, and simply increment from 2 in the original (ie =ifna(arrayformula(vlookup(F3:F, Sheet2!A2:D, 3,0))), etc)