Forum Discussion

WBT's avatar
WBT
Copper Contributor
Feb 20, 2026

Functions and formulars

Hi all, yes I am struggling to find the right formular for a problem, or to get it to work.

I have a number in cell B14, which i want to return a value in cell B13.

if B14 gives a value between 30 and 49 (this is manually entered) the result in B13 should be Pink (the word not a coloured cell) and 

if B14 gives a value between 17 and 33 the result in B13 should be Purple

plus a few more along these lines and if the B14 value is under 9 result should be Yellow

I am frustrated... this used to be simple for me but its been a while and any help would be appreciated.

 

excel
Formulas and Functions

4 Replies

  • IlirU's avatar
    IlirU
    Iron Contributor
    Feb 23, 2026

    Hi WBT​,

    First of all, I want to clarify the fact that in your explanation you have said that if the value in cell B14 is between:

    • 30 and 49  --> then the word Pink should be placed in cell B13
    • 17 and 33  --> then the word Purple should be placed in cell B13

    But between these two cases there is a wrong logic because if in cell B14, for example, the number 32 was placed, which result do you expect to come out of the formula: pink or purple? For this reason, I have set the data range for the word Purple between the numbers 17 and 29. In this case, try the following formula.

    =LOOKUP(B14, {0,9,17,30,50}, {"Yellow","-","Purple","Pink","-"})

    In the formula, I have set "-", for cases when the numbers placed in cell B14 are outside the range of values ​​that you have requested for the formula to work. Of course, in the above formula I have considered that only positive numbers will be placed in cell B14 (including the number zero).

    I hope I was clear in my explanation and I hope my formula works for you.

    IlirU

  • ThomasMcGurn's avatar
    ThomasMcGurn
    Copper Contributor
    Feb 22, 2026

    =IFS(B14<9, "Yellow", AND(B14>=17, B14<=29), "Purple", AND(B14>=30, B14<=49), "Pink", TRUE, "No Match")

    Yellow is less than 9 
    Purple is 17-29
    Pink is 30-49

    Anything outside those ranges will come back as "No Match". This should be pretty easy to edit in the future. Try out Co-pilot to help you create the formulas, you can type in exactly what you requested here and it would provide you what you need very quickly. 

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Feb 22, 2026

    Hello WBT​,

    You can use this nested IF formula in B13: =IF(B14<9,"Yellow",IF(B14<=16,"Orange",IF(B14<=33,"Purple",IF(B14<=49,"Pink",IF(B14<=60,"Blue","Other"))))). This works like this: less than 9 = Yellow, 9 to 16 = Orange, 17 to 33 = Purple, 34 to 49 = Pink, 50 to 60 = Blue, anything else = Other.

    A cleaner alternative is =CHOOSE(MATCH(B14,{0,9,17,34,50},1),"Yellow","Orange","Purple","Pink","Blue") where MATCH finds which range B14 falls into and CHOOSE returns the corresponding color word. This version is shorter and easier to maintain if you want to add more ranges later.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 22, 2026

    Place this formula in cell B13:

    =IFS(B14>=30, "Pink", B14>=17, "Purple", B14<9, "Yellow", TRUE, "")

     

    If you are using a version of Excel before 2019, you might not have the IFS function. In that case, you "nest" IF functions inside each other. The logic is identical, but the syntax is a bit messier.

    =IF(B14>=30, "Pink", IF(B14>=17, "Purple", IF(B14<9, "Yellow", "")))

     

    The VLOOKUP Method (Most Scalable)

    This is a more advanced but very powerful method, especially if you have many ranges (e.g., 10+ colors). It keeps your logic separate from your formula, making it very easy to update.

    =VLOOKUP(B14, D:E, 2, TRUE)

    All formulas in cell B13.

     

    I recommend the IFS function. It's the modern, clean, and correct way to solve this, my own opinion 🙂