Points for answer formula in excel

[jackfood]

Hello,
Using excel 2000.

I am creating a question which needs to creating points for answer.

Cell A187

Example:
What colour of car is expensive?
Answer:
blue, yellow, green.



typing in formula in Cell A188

If a person type blue, Cell A2 will get 1.
If a person type blue and green, Cell A2 will get 2 and so on.

I have 2 codes for this cell but only managed to get 1 point.

=IF( SUM(--ISNUMBER(SEARCH({"blue","green","yellow"}, A187))),"1","0")

another one is

=IF(ISNUMBER(SEARCH("blue",A187)),1,IF(ISNUMBER(SEARCH("green",A187)),1,0))



Can anyone improve or give me a new formula. Very much Thanks!!!

[teylyn]

maybe with a helper table.

Create a table somewhere in the workbook, where the first colum is the value that the user enters, and the second column is the points that are awarded for that answer. For example, if you create that table on Sheet2, starting in A1 with the first color and B1 with the point for the first color, and let's imagine this table is 10 rows. Then use a formula like

=VLOOKUP(A188,Sheet2!A1:B10,2,FALSE)

... in the cell A2, or where you want to see the points for that question.

does that help?

[etaf]

vlookup as suggested above is the way I would go

but an alternative
if its just ONE entry in cell A187 for question and CELL A188 for answer

I would put a validation on the Cell A187 with a drop down which only provides the options
blue, yellow, green.
in cell A188
=IF(A187="blue",1,2)
since if they do not choose blue the answer is 2