# Help with an IF formula

#### Sambarclay

##### New member
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

##### New member
An example workbook would help

#### Sambarclay

##### New member
Thanks for replying royUK. This is an example workbook.

#### Attachments

• example.xlsx
9.1 KB · Views: 16

#### JeffreyWeir

##### Super Moderator
Staff member
I understand that the same word might appear multiple times in one column. Question: Can the same word appear in multiple columns?

#### JeffreyWeir

##### Super Moderator
Staff member
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

##### New member
Fantastic, it works perfectly. Thanks.