Help with an IF formula

Sambarclay

New member
Joined
Jun 23, 2013
Messages
3
Reaction score
0
Points
0
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.
 

Sambarclay

New member
Joined
Jun 23, 2013
Messages
3
Reaction score
0
Points
0
Thanks for replying royUK. This is an example workbook.
 

Attachments

  • example.xlsx
    9.1 KB · Views: 16

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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))
 
Top