Sep 30 2021 10:47 PM
I NEED A FORMULA LIKE THIS
IF CELL K2 VALUE IS <5 RESULT SHOULD DISPLAY "NO DUES"
IF CELL K2 VALUE IS >5 AND <40 RESULT SHOULD DISPLAY "S DUES"
IF CELL K2 VALUE IS >40 AND <75 RESULT SHOULD DISPLAY "M DUES"
IF CELL K2 VALUE IS >75 AND <100 RESULT SHOULD DISPLAY "L DUES"
IF CELL K2 VALUE IS >100 AND <500 RESULT SHOULD DISPLAY "OVER DUE"
PLEASE HELP ME IN THIS
EMAIL ID : shrirameshgovind@gmail.com
Oct 01 2021 12:09 AM
@RAMESHGOVIND I would suggest a table with the limits and VLOOKUP to get the result;
=VLOOKUP(K2;O2:Q7;3)
The table can, if you like, be moved to another sheet with preferences (cut and paste).
Oct 01 2021 12:56 AM
@RAMESHGOVIND VLOOKUP and a lookup table is imho clear and easy to both maintain and pass on to another user that may iherit the model.
If you prefer all in one cell, you can use
=CHOOSE(MATCH(K2;{0;5;40;75;100;500});"No DUES";"S DUES";"M DUES";"L DUES";"OVER DUE";"#N/A")
It's been a while since I used 2007 but {0;5;40;75;100;500} is an array of numbers like P2:P6. Plus a leading zero.
An even more powerful alternative than choose is switch, but in this case choose is enough.
In later Excel versions, managing dynamic arrays, you can enter ={0;5;40;75;100;500} and see how they propagate from the anchor cell and downwards as a list of numbers.
Oct 01 2021 01:27 AM
As variant
=LOOKUP(K2, {0,5,40,75,100},{"NO DUES","S DUES","M DUES","L DUES","OVER DUE"})
and drag it down
Oct 01 2021 01:31 AM
Oct 01 2021 01:44 AM
SolutionGreat, thank you for the update
Oct 01 2021 01:44 AM
Solution