Code / Formula Suggestion

[PrashanthGM12345]

Hi i am trying to the following activity in excel. Please give me some suggestion of how to do it.
Activity: I have 6 Strings - Highly Dissatisfied, Dissatisfied, Neutral, Satisfied, Highly Satisfied, Not Applicable in excel which gets picked up from the survey automatically.
I want to develop a code / formula to pick score 0-Not Applicable , 1 - Highly Dissatisfied, 2 - Dissatisfied, 3 - Neutral, 4 - Satisfied, 5 - Highly Satisfied.

I have attached the Survey file for reference.

In the attached file please refer TO_BE worksheet

 

[NARAYANK991]

Hi ,

I am not clear on what you want done :

1. Which cells should have an output ?

2. What should the outputs be ?

3. How should these outputs be derived ?

Narayan

 

[Nightlytic]

See cell F5 in To be sheet

 

[Chihiro]

Did you mean F3?
You already have a formula that's working.

Explain more clearly what you need.

 

[Nightlytic]

Uh, it's F2, I think the row height is throwing us both off. And that was just my proposed solution for Prashanth.

 

[bosco_yip]

If F2 is the result cell, then try this,

1] Range D55:E60 is the Lookup Score Table,

reverse the order of D58:E58 and D59:E59 as per the attached file.

2] F2, formula copied down to F9 :

=IF(E2="","",LOOKUP(9^9,SEARCH(E2,$D$55:$D$60),$E$55:$E$60))

3] Select F2:F9, copy and paste to H2:H9, J2:J9...… etc.

Regards
Bosco