SOLVED
Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-468903%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468903%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20building%20an%20excel%20spreadsheet%20and%20I%20need%20help%20putting%20together%20a%20formula%20that%20allows%20me%20to%20choose%20a%20%24%20amount%20based%20on%20a%20%25%20range.%20let%20me%20explain%3A%3C%2FP%3E%3CP%3EI%20need%20the%20formula%20to%20look%20at%20a%20cell%2C%20let%20say%20E37%2C%20and%20based%20on%20the%20%25%20number%26nbsp%3B%20in%20that%20cell%2C%20to%20determine%20the%20associated%20%24%20amount%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20if%20E37is%20LESS%20than%205%25%20(cell%20D46)%2C%20then%20%24%20amount%20should%20be%20the%20amount%20located%20in%20cell%20G46%3C%2FP%3E%3CP%3E-%20if%20E37%20is%20BETWEEN%205%25%20and%209.99%25%20%3CSPAN%3E(cell%20D47)%3C%2FSPAN%3E%2C%20then%20the%20%24%20amount%20should%20be%20the%20number%20located%20in%20cell%20G47%3C%2FP%3E%3CP%3E%3CSPAN%3E-%20if%20E37%20is%20BETWEEN%2010%25%20and%2015%25%20(cell%20D48)%2C%20then%20the%20%24%20amount%20should%20be%20the%20number%20located%20in%20cell%20G48%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3E-%20if%20E37%20is%20MORE%20than%2015%25%20(cell%20D49)%2C%20then%20the%20%24%20amount%20should%20be%20the%20number%20located%20in%20cell%20G49.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3EDoes%20anyone%20know%20how%20to%20write%20such%20a%20formula%3F%20I%20tried%20using%20a%20multiple%20'IF%22%20formula%20but%20I%20cant%20get%20it%20to%20work...%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3Ethanks%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-468903%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468928%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323045%22%20target%3D%22_blank%22%3E%40AGuillot%3C%2FA%3E%2C%20that's%20like%3C%2FP%3E%0A%3CPRE%3E%3DLOOKUP(E37%2C%24D%2446%3A%24D%2449%2C%24G%2446%3A%24G%2449)%3C%2FPRE%3E%0A%3CP%3Eif%20only%20you%20change%20D49%20from%2015%25%20on%2015.0000001%25%20or%20so.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468947%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThat%20worked.%20awesome!%20Thankyou%20Sergei!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-468960%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-468960%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323045%22%20target%3D%22_blank%22%3E%40AGuillot%3C%2FA%3E%20%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
AGuillot
New Contributor

Hi,

 

I am building an excel spreadsheet and I need help putting together a formula that allows me to choose a $ amount based on a % range. let me explain:

I need the formula to look at a cell, let say E37, and based on the % number  in that cell, to determine the associated $ amount:

 

- if E37is LESS than 5% (cell D46), then $ amount should be the amount located in cell G46

- if E37 is BETWEEN 5% and 9.99% (cell D47), then the $ amount should be the number located in cell G47

- if E37 is BETWEEN 10% and 15% (cell D48), then the $ amount should be the number located in cell G48

- if E37 is MORE than 15% (cell D49), then the $ amount should be the number located in cell G49.

 

Does anyone know how to write such a formula? I tried using a multiple 'IF" formula but I cant get it to work...

 

thanks

3 Replies
Highlighted
Solution

@AGuillot, that's like

=LOOKUP(E37,$D$46:$D$49,$G$46:$G$49)

if only you change D49 from 15% on 15.0000001% or so.

 

@Sergei BaklanThat worked. awesome! Thankyou Sergei!!!!

@AGuillot , you are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies