Help with an IF formula

[Sambarclay]

Hi all,

I need your help with an IF formula.
I have four columns. Columns A-C have 10 cells (each cell contains a word, some words appear more than once in the same column). My students will write their answers to a vocabulary test in cell D1. If their answer (i.e. D1) matches any of the words in column A, I want to return a score of 2 in E1. If it's in column B I want to return a score of 1, and 0 if it's in C. Also, if their answer is not in any of the columns, I'd like to copy their answer into F1. I thought I found a solution using 'countif', but it couldn't handle words that occurred twice or more in the same column (e.g. it gave them a score of 4 if a word appeared twice in column A).

Could you help me? Any suggestions would be greatly appreciated.

[royUK]

An example workbook would help

[Sambarclay]

Thanks for replying royUK. This is an example workbook.

[JeffreyWeir]

I understand that the same word might appear multiple times in one column. Question: Can the same word appear in multiple columns?

[JeffreyWeir]

Assuming the answer to my previous question is no, then put this in E2 and copy down:
=IFERROR((MATCH(D2,$A$2:$A$11,0)>0)*2,0)+IFERROR((MATCH(D2,$B$2:$B$11,0)>0)*1,0)

And put this array formula into cell F2, enter it by pushing Ctrl+Shift+Enter, then copy down:
=REPT(D2,IF(SUM(IFERROR(FIND(D2,A2:C11),0))=0,1,0))

[Sambarclay]

Fantastic, it works perfectly. Thanks.