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
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies