IF AND CONTAINS Formula that Returns Value

[onlyonekj]

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:

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.

 

[Eric W]

Maybe:

=LOOKUP(F8,{0,1,2,4,6},COUNTIF(A8,"*relevant*")*{0,8,17,28,35}+COUNTIF(A8,"*related*")*{0,2,4,6,8})

 

[onlyonekj]

Hi Eric,

This works perfectly!!! Thank you so much!!! Happy Holidays! ?

 

[Eric W]

Happy to help! ? Happy Holidays to you too!