Forum Discussion
Return a value based on different parameters
Not sure if my title is describing my issue correctly. I have one column where each cell contains the same drop down list from which I can choose one of six options, and depending on what option is chosen, I want the cell in another column to return certain value:
| Option Chosen | Return Value |
| Not Started | 0% |
| Started | 25% |
| In Progress | 50% |
| Nearly There | 75% |
| Complete | 100% |
I found this example formula online, which is only repeated once but works perfectly for two parameters:
=IF(ISNUMBER(SEARCH("Not Started",F3)),"0%",IF(ISNUMBER(SEARCH("Started",F3)),"25%"))
However, when I start to extend/repeat the formula more than once for the remaining parameters I cannot get it to work. I've extended it as follows...
=IF(ISNUMBER(SEARCH("Not Started",F3)),"0%",IF(ISNUMBER(SEARCH("Started",F3)),"25%",IF(ISNUMBER(SEARCH("In Progress",F3)),"50%",IF(ISNUMBER(SEARCH("Nearly There",F3)),"75%",IF(ISNUMBER(SEARCH("Complete",F3)),"100%"))
... and it returns the error 'the formula is missing an opening or closing parenthesis'
Please can anyone help with this.
Thank you in advance.
=IF(ISNUMBER(SEARCH("Not Started",F3)),"0%", IF(ISNUMBER(SEARCH("Started",F3)),"25%", IF(ISNUMBER(SEARCH("In Progress",F3)),"50%", IF(ISNUMBER(SEARCH("Nearly There",F3)),"75%", IF(ISNUMBER(SEARCH("Complete",F3)),"100%","")))))This formula works in my sample file.
4 Replies
I would recommend you to avoid nested IF functions with hard-coded values, especially when you need many of them. Better to use a structured table with Option Chosen and Return Value as a lookup table for the XLOOKUP function. Call the table "options", for example. Then the following will work just fine.
=XLOOKUP(F3,options[Option Chosen],options[Return Value])And then you can point the drop down list to the first column of the options table. Add or delete options later and the drop-down will automatically update.
Other ways (just a few examples) that avoid nested IF functions (though still with hard-coded values) could be:
=SUM(--(F3={"Not Started","Started","In Progress","Nearly There","Complete"})*{0,0.25,0.5,0.75,1})or
=SWITCH(F3, "Not Started",0,"Started",0.25,"In Progress",0.5,"Nearly There",0.75,"Complete",1)Thank you so much. I'm very grateful for you taking the time to answer my query. I'm sure this would have worked, but then I saw Oliver's reply and it was easier to copy and paste that. But thanks again. 🙏
=IF(ISNUMBER(SEARCH("Not Started",F3)),"0%", IF(ISNUMBER(SEARCH("Started",F3)),"25%", IF(ISNUMBER(SEARCH("In Progress",F3)),"50%", IF(ISNUMBER(SEARCH("Nearly There",F3)),"75%", IF(ISNUMBER(SEARCH("Complete",F3)),"100%","")))))This formula works in my sample file.
Thank you so much. This has worked for me. I'm very grateful! 🙏
