SOLVED

# help me with creating a formula

Occasional Contributor

# help me with creating a formula

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"

EMAIL ID : shrirameshgovind@gmail.com

12 Replies

# Re: help me with creating a formula

USING MS EXCEL 2007
OS WINDOWS 7 ULTIMATE

# Re: help me with creating a formula

@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).

# Re: help me with creating a formula

LET ME TRY. THANKS FOR YOUR TIME

# Re: help me with creating a formula

@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.

# Re: help me with creating a formula

IT IS NOT FOR ONE CELL FROM K2 TO K 1293

# Re: help me with creating a formula

As variant

``=LOOKUP(K2, {0,5,40,75,100},{"NO DUES","S DUES","M DUES","L DUES","OVER DUE"})``

and drag it down

# Re: help me with creating a formula

THANKS SIR, BUT I NEED THIS RESULT IN ENTIRE WORK BOOK. PLS LOOK FORWARD THE IMG WHICH I UPLOADED. THANKS FOR THE VALUABLE TIME

# Re: help me with creating a formula

THANKS IT WORKED
best response confirmed by RAMESHGOVIND (Occasional Contributor)
Solution

# Re: help me with creating a formula

Great, thank you for the update