Forum Discussion
Return a value based on different parameters
=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.
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. 🙏
