I need help, 5 >=/<= possibilities each with a specific answer, Please Help!

%3CLINGO-SUB%20id%3D%22lingo-sub-1655030%22%20slang%3D%22en-US%22%3EI%20need%20help%2C%205%20%26gt%3B%3D%2F%26lt%3B%3D%20possibilities%20each%20with%20a%20specific%20answer%2C%20Please%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1655030%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20needing%20a%20formula%20which%20essentially%20says%3A%3C%2FP%3E%3CP%3EIf%20a%20cell%20value%20is%20%26gt%3B%3DA%20and%20%26lt%3B%3DB%20then%20%22p%25%22%2C%20if%20not%20and%20cell%20value%20is%20%26gt%3B%3DC%20and%20%26lt%3B%3DD%20then%20%22q%25%22%2C%20if%20not%20and%20cell%20value%20is%20%26gt%3B%3DE%20and%20%26lt%3B%3DF%20the%20%22r%25%22%2C%20etc...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1655030%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1655210%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%2C%205%20%26gt%3B%3D%2F%26lt%3B%3D%20possibilities%20each%20with%20a%20specific%20answer%2C%20Please%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1655210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F789466%22%20target%3D%22_blank%22%3E%40Bill_Win%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20are%20the%20ranges%20related.%26nbsp%3B%20Do%20they%20overlap%3B%20are%20there%20gaps%3F%3C%2FP%3E%3CP%3EMicrosoft%20365%20only%2C%20but%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FILTER(%20percent%2C%20(value%26gt%3B%3Dlower)*(value%26lt%3B%3Dupper)%2C%20%22Not%20in%20range%22%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewould%20take%20care%20of%20most%20eventualities.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1655218%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%2C%205%20%26gt%3B%3D%2F%26lt%3B%3D%20possibilities%20each%20with%20a%20specific%20answer%2C%20Please%20Help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1655218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BThanks%20for%20reaching%20out.%20The%20ranges%20butt%20up%20against%20each%20other.%26nbsp%3B%20For%20example%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20cell%20is%20greater%20than%20or%20equal%20to%2010.01%25%20but%20less%20than%20or%20equal%2020.00%25%20then%20%2225%25%2C%20if%20not%20and%20cell%20is%20greater%20than%20or%20equal%20to%2020.01%25%20but%20less%20than%20or%20equal%20to%2030.00%25%20then%20%2235%25%2C%20and%20so%20on%20and%20so%20forth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20look%20forward%20to%20your%20response!%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am needing a formula which essentially says:

If a cell value is >=A and <=B then "p%", if not and cell value is >=C and <=D then "q%", if not and cell value is >=E and <=F the "r%", etc...

 

Any help would be appreciated.

3 Replies
Highlighted

@Bill_Win 

How are the ranges related.  Do they overlap; are there gaps?

Microsoft 365 only, but

= FILTER( percent, (value>=lower)*(value<=upper), "Not in range" )

would take care of most eventualities. 

Highlighted

@Peter Bartholomew Thanks for reaching out. The ranges butt up against each other.  For example: 

 

If cell is greater than or equal to 10.01% but less than or equal 20.00% then "25%, if not and cell is greater than or equal to 20.01% but less than or equal to 30.00% then "35%, and so on and so forth.

 

I look forward to your response!   

Highlighted

@Bill_Win 

Since there are no overlaps you could use a lookup.  Options with XLOOKUP are

= XLOOKUP(value, lower, percent, ,-1)

= XLOOKUP(value, upper, percent, ,1)

but the older LOOKUP requires the lower bound

= LOOKUP( value, lower, percent )