I am fairly new with formulas and really could use some assistance. I am trying to create a formula that will look for a certain word in cell A8 (RELEVANT or RELATED) as well as look at the value in cell F8 (the value of a number) to return a certain value in cell G8. I have a Data Validation List in cell A8 and once a selection is made I want the formula to look for the word RELEVANT or RELATED and also look in cell F8 at the number and return a certain value (pts that are listed in the Data Validation List) for the selection made in A8.
Data Validation List is:
The formula I tried that is not working:
Formula:
=IF(ISNUMBER(SEARCH("RELEVANT",A8,AND(F8=<6,35,0))))
Of course this returns an error: "There's a problem with this formula. Not trying to type a formula? When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula: ...."
Thanks.
Data Validation List is:
| None= 0 pts |
| RELEVANT: 6+ yrs= 35 pts |
| RELEVANT: 4-5 yrs= 28 pts |
| RELEVANT: 2-3 yrs= 17 pts |
| RELEVANT: <2 yrs>1 yr= 8 pts |
| RELATED: 6+ yrs= 8 pts |
| RELATED: 4-5 yrs= 6 pts |
| RELATED: 2-3 yrs= 4 pts |
| RELATED: <2 yrs>1 yr= 2 pts |
The formula I tried that is not working:
Formula:
=IF(ISNUMBER(SEARCH("RELEVANT",A8,AND(F8=<6,35,0))))
Of course this returns an error: "There's a problem with this formula. Not trying to type a formula? When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula: ...."
Thanks.
