Forum Discussion

FreddyNoel's avatar
FreddyNoel
Copper Contributor
Sep 30, 2020
Solved

If Condition with text and multiple options

Hello, I am new to formula and trying to create one that shows a different result in the column Score "E" every time a number is entered in the Stars column "D". If 5 = Gold, if 4 = Silver, picked ...
Excel on Mac
Formulas and Functions
  • ExcelExciting's avatar
    ExcelExciting
    Sep 30, 2020

    Hi FreddyNoel ,

     

    There are many ways to handle it, please find below you may construct your formula using VLOOKUP. your source data should be in ascending order to get the results.

     

    =VLOOKUP(D4,$A$9:$B$13,2,TRUE)

     

     

    Attached is the sample file for your reference

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Sep 30, 2020

FreddyNoel 

 

If I may recommend you add a file (without sensitive data) to show your project instead of a picture.
You have to consider that the helper has to make the effort to design the file that already exists.
This is very tiring from the start, so it is always good to insert a file.
This way you will be helped faster and the helpers too, whereby they can give a faster and more tailored answer.
 
Thank you in advance for your time and effort.
 

Nikolino

I know I don't know anything (Socrates)

 
FreddyNoel's avatar
FreddyNoel
Copper Contributor
Sep 30, 2020

Thank you, Nikolino, for your quick reply.

My apologies for inserting a picture instead of the file.

This was my first post and I am already learning.

Best regards

Freddy

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 30, 2020
    Everything is OK. You have received from Mr. Faraz Shaikh a proposed solution that suits you, as far as I have seen. I can't expect better than that.

    Nevertheless, I would recommend adding a file (without sensitive data) the next time.

    Thank you for your understanding

    Nikolino
    I know I don't know anything (Socrates)
    • FreddyNoel's avatar
      FreddyNoel
      Copper Contributor
      Oct 01, 2020

      NikolinoDE and ExcelExciting

       

      Thank you both for your input and if I may ask one more favour, it would make my spreadsheet amazing. As per Nikolino recommendation, I attach a file for easier editing.

      Is there a possibility to add to the "=VLOOKUP(D9,$A$16:$B$20,2,1)" a complementary formula that would make my Gold rating cell coloured Yellow, Silver rating cell coloured Grey and Bronze rating cell coloured Brown? The colour could either be the text or the fill-in cell.

      Thank you again for your amazing support.

      Best regards

      FreddyNoel

       

       

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Oct 01, 2020

        FreddyNoel 

        In the inserted file you can see the formula in action.

        in German:

        =WENNFEHLER(SVERWEIS(D10;$A$16:$B$20;2);"")

        in English:

        =IFERROR(VLOOKUP(D10,$A$16:$B$20,2),"")

        So you no longer have an error message ... if there is no value in D then E is also empty.

         

         

         

        Hope I was able to help you and would be happy to know if I could help you.

         

        Nikolino

        I know I don't know anything (Socrates)

         

Resources