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

2 Replies

  • 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 🙂