SOLVED

Using IF with AND, OR

%3CLINGO-SUB%20id%3D%22lingo-sub-2277897%22%20slang%3D%22en-US%22%3EUsing%20IF%20with%20AND%2C%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277897%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20was%20hoping%20for%20some%20assistance%2C%20I%20am%20an%20excel%20novice%20and%20I%20was%20looking%20to%20produce%20a%20formula%20that%20would%20enable%20me%20to%20show%20the%20following%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EIf%20%3CSTRONG%3EE11%26gt%3B%3DA1%2CE11%26lt%3B%3DB1%20then%20E12%20%3D2.00%25%3C%2FSTRONG%3E%20or%20%3CSTRONG%3Eif%26nbsp%3BE11%26gt%3B%3DA2%2CE11%26lt%3B%3DB2%20then%20E12%20%3D1.75%25%2C%26nbsp%3Bor%20if%26nbsp%3BE11%26gt%3B%3DA3%2CE11%26lt%3B%3DB3%20then%20E12%20%3D1.50%25%26nbsp%3B%20etc%26nbsp%3B%3C%2FSTRONG%3Ethe%20formula%20would%20the%20go%20on%20with%20these%20conditions%20until%26nbsp%3B%C2%A3100%2C000%2C000.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22403px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3EA%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3EB%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3EC%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3ED%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3EE%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A30%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A325%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E2.00%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E2.00%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A325%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A350%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.75%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.75%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A350%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A3100%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.50%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.50%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E4%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A3100%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A3250%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.25%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.25%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E5%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A3250%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A3500%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.00%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E1.00%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E6%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A3500%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A31%2C000%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.70%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.70%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E7%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A31%2C000%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A33%2C000%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.65%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.65%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E9%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A33%2C000%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A35%2C000%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.60%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.60%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E10%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%C2%A35%2C000%2C001%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%C2%A3100%2C000%2C000%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.55%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E0.55%25%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E11%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E%C2%A315%2C000%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%3CP%3E12%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2287px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22104px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2266px%22%3E%3CP%3E2.00%25%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2277897%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278079%22%20slang%3D%22en-US%22%3ERE%3A%20Using%20IF%20with%20AND%2C%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1030353%22%20target%3D%22_blank%22%3E%40FredExcel%3C%2FA%3E%26nbsp%3BYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2277952%22%20slang%3D%22en-US%22%3ERE%3A%20Using%20IF%20with%20AND%2C%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277952%22%20slang%3D%22en-US%22%3EThanks%2C%20much%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2277943%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20with%20AND%2C%20OR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2277943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1030353%22%20target%3D%22_blank%22%3E%40FredExcel%3C%2FA%3E%26nbsp%3BThat%20would%20be%20in%20E12%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(E10%2CA1%3AA9%2CC1%3AC9)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I was hoping for some assistance, I am an excel novice and I was looking to produce a formula that would enable me to show the following

 

If E11>=A1,E11<=B1 then E12 =2.00% or if E11>=A2,E11<=B2 then E12 =1.75%, or if E11>=A3,E11<=B3 then E12 =1.50%  etc the formula would the go on with these conditions until £100,000,000.

 

 

 

A

B

C

D

E

1

£0

£25,000

2.00%

2.00%

 

2

£25,001

£50,000

1.75%

1.75%

 

3

£50,001

£100,000

1.50%

1.50%

 

4

£100,001

£250,000

1.25%

1.25%

 

5

£250,001

£500,000

1.00%

1.00%

 

6

£500,001

£1,000,000

0.70%

0.70%

 

7

£1,000,001

£3,000,000

0.65%

0.65%

 

9

£3,000,001

£5,000,000

0.60%

0.60%

 

10

£5,000,001

£100,000,000

0.55%

0.55%

 

11

 

 

 

 

£15,000

12

 

 

 

 

2.00%

 

3 Replies
best response confirmed by FredExcel (New Contributor)
Solution

@FredExcel That would be in E12:

=LOOKUP(E10,A1:A9,C1:C9)

 

Thanks, much appreciated.

@FredExcel You're welcome!