# 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.  Reply With Quote

2. An example workbook would help  Reply With Quote

3. Thanks for replying royUK. This is an example workbook.  Reply With Quote

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

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))  Reply With Quote

6. Fantastic, it works perfectly. Thanks.  Reply With Quote

#### Posting Permissions

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