In Excel, how to write an IF formula where the right answer can be partial or with multiple entries?

Question

I'm working on a vocabulary practice sheet.

I need to be able to input various possible translations for a word in one cell and get a true/false in another cell.

For example, the vocab word in English is "demand" and in French it may be translated as "demander", "réclamer", or "sous commande".

I'm using the following formula to have any of these as possible answers: =IF(OR(B7="demander",B7="réclamer",B7="s... commandel"),1,0).

But I also need to be able to input several of these in the same cell separated by a comma, in what ever order. I thought of putting the possible answers in separate cells (i.e. f7, g7, h7) and then call these in the formula. But how can I include all of them as a logical answer?

Thanks for your help.

Answer 1

Hi I Think you can use named range for this. This would work for 65536 words only.

For Example:

Create one sheet lets say "Translations", in row 1

values in A1=Demander, B1=réclamer, C1=sous commande and so on till IV1.

Select Range A1 till IV1 and from Menu-->Insert-->Name--Define--> give the name as "Demand" and click add

Same way do it for another word from A2 till IV2 and name the range as the word for which you have entered the translations.

Then In another sheet lets say "Translator".

Let us assume A1 will have the English word and B1 will have the translation of the same. Then in C1 enter the following formula

=IF(ISERROR(VLOOKUP(B1,

INDIRECT(A1),1,0)),"False"

,"True")

If it confuses you then send me an email with the list of words and there translation and I'll try to create the workbook for you.