Help - looking for advise with IF and AND formula (German wenn / und)?

[MSchädler]

Hi MrExcel,
I would like to find a easier way using my excel formula and maybe someone can give me some advice.
The formula I have set up and is working is as follow (in german):
=WENN(UND(A3<=KIAO!$G$10;KIAO!B3=KIAO!$E$10);KIAO!$H$10;WENN(UND(A3<=KIAO!$G$11;KIAO!B3=KIAO!$E$10);KIAO!$H$11;WENN(UND(A3>=KIAO!$G$12;KIAO!B3=KIAO!$E$10);KIAO!$H$12;WENN(UND(A3<=KIAO!$G$13;B3=KIAO!$E$13);KIAO!$H$13;WENN(UND(A3>KIAO!$G$13;A3<=KIAO!$G$14;B3=KIAO!$E$13);KIAO!$H$14;WENN(UND(A3>KIAO!$G$14;B3=KIAO!$E$13);KIAO!$H$15;WENN(KIAO!$G$14;KIAO!$H$15;FALSCH)))))))

My sheet is setup as follow (see image) and I have adjusted the excel sheet so that I can place the question.
Description:
- In column A we have length sizes and this is entered manually.
- In column B we can choose (drop down) between standard or reduced (from E10 to E15).
- Column C should give the result (price) according to the different parameters of the table, Column E10 to H15.
To make my formula work I had to introduce Column G with fixed numbers.
The 3 categories in column F are: less than 25 (<25), between 25 and 40 (25-40), over 40 (>40).

Example of the result in a cell in column C;
if 15metre is entered (column A) and a standard case is picked (column B) the formula has to figure, the entered metre is in what of the three parameters (<25; 25-40; >40). Then from column B we defined Standard or Reduced and then the formula should return the right price into column C.
The result in this case is: 2'100.00

Is there a easier formula for this task?
Thanks for any input.

 

[DRSteele]

Ja, there is a much easier method.

Copy downwards the formula in C3. I translated it into Deutsch: =INDEX($F$6:$G$8;VERGLEICH(A3;$E$6:$E$8);VERGLEICH(B3;$F$5:$G$5;0))

MrExcel posts18.xlsx
	A	B	C	D	E	F	G
2	length (m)	S/R	Price
3	0.00	standard	2,100.00
4	0.00	reduced	1,380.00
5	0.01	standard	2,100.00		length (m)	Standard	Reduced
6	0.01	reduced	1,380.00		0.00	2,100.00	1,380.00
7	24.99	standard	2,100.00		25.00	3,200.00	1,520.00
8	24.99	reduced	1,380.00		40.00	3,400.00	1,470.00
9	25.00	standard	3,200.00
10	25.00	reduced	1,520.00
11	25.01	standard	3,200.00
12	25.01	reduced	1,520.00
13	40.00	standard	3,400.00
14	40.00	reduced	1,470.00
15	40.01	standard	3,400.00
16	40.01	standard	3,400.00
17	40.01	reduced	1,470.00
18	40.01	reduced	1,470.00
19	41.00	standard	3,400.00
20	41.00	reduced	1,470.00
Sheet36

Cell Formulas
Range	Formula
C3:C20	C3	=INDEX($F$6:$G$8,MATCH(A3,$E$6:$E$8),MATCH(B3,$F$5:$G$5,0))

 

[MSchädler]

Hello DRSteele, great input.
I implemented your approach in my excel sheet and made it work.
Your way is also a lot easier if I have to change the numbers in my table,
Thanks a lot, it works just fine.
Regards
Marc

 

[DRSteele]

Super. You're welcome.

I was worried I might end up guilty of Verschlimmbesserung.