# Thread: Help with an IF formula

1. ## Help with an IF formula

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.

2. An example workbook would help

3. Thanks for replying royUK. This is an example workbook.

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

5. 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))

6. Fantastic, it works perfectly. Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•