Query related to Dropdown list in Excel

Super Contributor

Hello Everyone, 

Here is a screenshot :

Annotation 2022-09-15 152656.png


What I want here is when user selects the Drop down list "marked red".

Here I need two things as solution.

1. ---  I need to map the Drop down items like -Not started, Completed etc to the Numbers mentioned in Brackets.


2. --- When user selects the Dropdown list, The corresponding number should automatically populate on Column (Rate when option selected). Ex:- 3 populate upon second option (Started and initial phase)


Please explain with example..


Please help...

4 Replies


Create a lookup list as in the screenshot below. Here, it is on the same sheet, but it can be on another sheet if you prefer.

The formula in C2 is


This can be filled down if required.


It means that :
1st - i have to create dropdownlist in Option list column.
2nd - Then i have to put formula in C2
then it will come Rate according to Option list.

Is this correct sir ?
best response confirmed by Excel (Super Contributor)



@Hans Vogelaar 

Hello Sir, 


Last solution of question which you have given suggestion is very helpful. 

And i apologize that i ask so many questions. 


Before i ask question here, first of all i will try, 

and If i do not get solution then i will ask question here.


I respect you a lot sir which you guide me. 



Please please please forgive me sir

I am very very sorry