Results 1 to 6 of 6

Thread: Help with an IF formula

  1. #1

    Help with an IF formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    An example workbook would help
    Hope that helps

    Roy

  3. #3
    Thanks for replying royUK. This is an example workbook.
    Attached Files Attached Files

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    I understand that the same word might appear multiple times in one column. Question: Can the same word appear in multiple columns?

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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. #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
  •