Forum Discussion
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 ...
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 🙂