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

#### MSchädler

##### Board Regular
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.

#### Attachments

• Excel IF.JPG
62.6 KB · Views: 3

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### DRSteele

##### Well-known Member
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
ABCDEFG
2length (m)S/RPrice
30.00standard2,100.00
40.00reduced1,380.00
50.01standard2,100.00length (m)StandardReduced
60.01reduced1,380.000.002,100.001,380.00
724.99standard2,100.0025.003,200.001,520.00
824.99reduced1,380.0040.003,400.001,470.00
925.00standard3,200.00
1025.00reduced1,520.00
1125.01standard3,200.00
1225.01reduced1,520.00
1340.00standard3,400.00
1440.00reduced1,470.00
1540.01standard3,400.00
1640.01standard3,400.00
1740.01reduced1,470.00
1840.01reduced1,470.00
1941.00standard3,400.00
2041.00reduced1,470.00
Sheet36
Cell Formulas
RangeFormula
C3:C20C3=INDEX(\$F\$6:\$G\$8,MATCH(A3,\$E\$6:\$E\$8),MATCH(B3,\$F\$5:\$G\$5,0))

#### MSchädler

##### Board Regular
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

##### Well-known Member
Super. You're welcome.

I was worried I might end up guilty of Verschlimmbesserung.

Replies
2
Views
96
Replies
1
Views
43
Replies
2
Views
156
Replies
6
Views
49
Replies
1
Views
94