Formula with IF, AND, INDEX, MATCH and SUM returning N/A

OneChief · May 10, 2021

Good Morning,

I am trying to Sum a combination of cells with IF, AND, INDEX and MATCH.
Individually the statement works. Also when all cells have values the statement sometimes works and sometimes produces a #ref Error..
Examples below.
Scenario:
One table holds values "Front Height 1, 2, 3" and "Front 1, 2 ,3 Width"

The next lot of cells have a formula to set up the values for an INDEX, MATCH to then produce a price from a pricing matrix.

=IF(B2<=0,0,IF(B2<=200,200,IF(B2<=400,400,IF(B2<=600,600,IF(B2<=750,750,IF(B2<=900,900,IF(B2<=1200,1200,IF(B2<=1500,1500,IF(B2<=1800,1800,IF(B2<=2100,2100,IF(B2<=2400,2400,IF(B2<=2700,2700,IF(B2<=3050,3050,"0")))))))))))))

Cell S2 (and beyond) then has the statement that I need to work.
It looks at 2 conditions being S1 = PM_Style01_TextAsh (Which is a table that produces pricing based on a height and width)

and if R2 = Door.
This is what I have that I thought was correct but is not working. I have tried a variety of IFS and SUMIF etc, with varied results non of which I have managed to be correct.
=IF(AND($S$1="PM_Style01_TextAsh",R2="Door"),SUM(INDEX(PM_Style01_TextAsh,MATCH(L2,PM_Style01_TextAshH),MATCH(M2,PM_Style01_TextAshW)),INDEX(PM_Style01_TextAsh,MATCH(N2,PM_Style01_TextAshH),MATCH(O2,PM_Style01_TextAshW),INDEX(PM_Style01_TextAsh,MATCH(P2,PM_Style01_TextAshH),MATCH(Q2,PM_Style01_TextAshW)))))

Thank you for your wisdom

Chief.

kweaver · May 10, 2021

Please post your sample data using XL2BB so those trying to help don't have to retype the data. Thanks.

OneChief · May 10, 2021

kweaver said:
Please post your sample data using XL2BB so those trying to help don't have to retype the data. Thanks.

Ok, I haven't used that. Ill look for it now. Thank you

kweaver · May 10, 2021

Look on the tool bar just above the typing area.

Also, give your example, what do you want as the result?

OneChief · May 10, 2021

First time using XL2BB Add in. Forgive me if I Have not done it correctly.

BID_Pricing.xlsm

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Front Height 1

Front Height 2

Front Height 3

Front Height 4

Front Height 5

Front Height 6

Front 1 Width

Front 2 Width

Front 3 Width

H1 (Lookup)

W1 (Lookup)

H2 (Lookup)

W2 (Lookup)

H3 (Lookup)

W3 (Lookup)

PM_Style01_TextAsh

PM_Style02_TextAsh

2

380

147

Door

3

380

197

WallCabinets

Cell Formulas
Range	Formula
L2	L2	=IF(B2<=0,0,IF(B2<=200,200,IF(B2<=400,400,IF(B2<=600,600,IF(B2<=750,750,IF(B2<=900,900,IF(B2<=1200,1200,IF(B2<=1500,1500,IF(B2<=1800,1800,IF(B2<=2100,2100,IF(B2<=2400,2400,IF(B2<=2700,2700,IF(B2<=3050,3050,"0")))))))))))))
M2	M2	=IF(H2<=0,0,IF(H2<=150,150,IF(H2<=300,300,IF(H2<=350,350,IF(H2<=400,400,IF(H2<=450,450,IF(H2<=500,500,IF(H2<=550,550,IF(H2<=600,600,IF(H2<=650,650,IF(H2<=800,800,IF(H2<=1000,1000,IF(H2<=1200,1200,"ERROR")))))))))))))
N2	N2	=IF(C2<=0,0,IF(C2<=200,200,IF(C2<=400,400,IF(C2<=600,600,IF(C2<=750,750,IF(C2<=900,900,IF(C2<=1200,1200,IF(C2<=1500,1500,IF(C2<=1800,1800,IF(C2<=2100,2100,IF(C2<=2400,2400,IF(C2<=2700,2700,IF(C2<=3050,3050,"0")))))))))))))
O2	O2	=IF(I2<=0,0,IF(I2<=200,200,IF(I2<=400,400,IF(I2<=600,600,IF(I2<=750,750,IF(I2<=900,900,IF(I2<=1200,1200,IF(I2<=1500,1500,IF(I2<=1800,1800,IF(I2<=2100,2100,IF(I2<=2400,2400,IF(I2<=2700,2700,IF(I2<=3050,3050,"0")))))))))))))
P2	P2	=IF(D2<=0,0,IF(D2<=200,200,IF(D2<=400,400,IF(D2<=600,600,IF(D2<=750,750,IF(D2<=900,900,IF(D2<=1200,1200,IF(D2<=1500,1500,IF(D2<=1800,1800,IF(D2<=2100,2100,IF(D2<=2400,2400,IF(D2<=2700,2700,IF(D2<=3050,3050,"0")))))))))))))
Q2	Q2	=IF(J2<=0,0,IF(J2<=200,200,IF(J2<=400,400,IF(J2<=600,600,IF(J2<=750,750,IF(J2<=900,900,IF(J2<=1200,1200,IF(J2<=1500,1500,IF(J2<=1800,1800,IF(J2<=2100,2100,IF(J2<=2400,2400,IF(J2<=2700,2700,IF(J2<=3050,3050,"0")))))))))))))
S1:AN1	S1	=TRANSPOSE(Pt_PriceGroupT[Price Groups (Polytec)])
S2	S2	=IF(AND($S$1="PM_Style01_TextAsh",R2="Door"),SUM(INDEX(PM_Style01_TextAsh,MATCH(L2,PM_Style01_TextAshH),MATCH(M2,PM_Style01_TextAshW)),INDEX(PM_Style01_TextAsh,MATCH(N2,PM_Style01_TextAshH),MATCH(O2,PM_Style01_TextAshW),INDEX(PM_Style01_TextAsh,MATCH(P2,PM_Style01_TextAshH),MATCH(Q2,PM_Style01_TextAshW)))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
PM_Style01_TextAsh	=Pt_Thermo_18mm!$C$8:$N$19	S2
PM_Style01_TextAshH	=Pt_Thermo_18mm!$B$8:$B$19	S2
PM_Style01_TextAshW	=Pt_Thermo_18mm!$C$7:$N$7	S2

kweaver · May 10, 2021

What answer should be in S2? 21.82, maybe?

kweaver · May 10, 2021

No, 17.22 ??

kweaver · May 10, 2021

What do you think?

May102021.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

F Hgt 1

F Hgt 2

F Hgt 3

F Hgt 4

F Hgt 5

F Hgt 6

F Wid 1

F Wid 2

F Wid 3

H1 (lookup)

W1 (lookup)

H2

W2

H3

W3

PM_Style01_TextAsh

2

390

147

Door

3

450

197

Door

Sheet9

Cell Formulas
Range	Formula
L2:L3	L2	=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=B2,0),1),0)
M2:M3	M2	=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=H2,0)),0)
N2:N3	N2	=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=C2,0),1),0)
O2:O3	O2	=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=I2,0)),0)
P2:P3	P2	=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=D2,0),1),0)
Q2:Q3	Q2	=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=J2,0)),0)
S2:S3	S2	=IF(AND("Door" =R2,"PM_Style01_TextAsh"=$S$1),INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=$B2,0),MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=$H2,0)),"")

OneChief · May 10, 2021

kweaver said:
What answer should be in S2? 21.82, maybe?

Ok, so if L2 and M2 have measurements (400 x 150) only the answer will be 17.22.
If L2, M2 and N2, O2 have measurements (400 x 150) the answer will be 34.44
If L2, M2 and N2, O2 and P2, Q2 have measurements (400 x 150) the answer will be 51.66

This is the Matrix Table it is getting the prices from.

BID_Pricing.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

5

Width mm

6

Height mm

100 - 150*

151 - 300

301 - 350

351 - 400

401 - 450

451 - 500

501 - 550

551 - 600

601 - 650

651 - 800

801 - 1000

1001 - 1200

7

150

300

350

400

450

500

550

600

650

800

1000

1200

8

35 - 200*

200

14.43

17.03

17.89

18.76

19.61

20.49

21.34

22.23

23.07

25.68

29.12

32.58

9

201 - 400

400

17.22

21.82

23.34

24.86

26.40

27.91

29.46

30.98

32.53

37.11

43.22

49.34

10

401 - 600

600

20.00

26.62

28.79

30.98

33.18

35.38

37.57

39.77

41.95

48.55

57.33

66.10

11

601 - 750

750

22.09

30.19

32.90

35.59

38.27

40.96

43.66

46.36

49.05

57.12

67.89

78.66

12

751-900

900

24.21

33.78

36.99

40.15

43.36

46.56

49.74

52.93

56.12

65.69

78.47

91.24

13

901-1200

1200

28.39

40.96

45.17

49.34

53.53

57.71

61.91

66.10

70.29

82.86

99.61

116.38

14

1201-1500

1500

32.58

48.16

53.33

58.51

63.71

68.89

74.07

79.27

84.44

100.00

120.76

141.50

15

1501-1800

1800

36.76

55.33

61.52

67.69

73.89

80.05

86.24

92.43

98.60

117.17

141.90

194.09

16

1801-2100

2100

40.96

62.49

69.69

76.87

84.05

91.24

98.42

105.60

112.80

134.33

163.05

221.76

17

2101-2400

2400

45.16

69.69

77.88

86.07

94.22

102.40

110.60

118.77

126.96

151.48

184.21

249.39

18

2401 - 2700

2700

50.82

79.94

89.67

99.37

109.08

118.76

128.50

138.20

147.93

177.04

215.87

287.17

19

2701 - 3050

3050

59.27

95.00

106.89

118.80

130.70

142.58

154.51

166.41

178.31

214.03

261.64

341.70

20

Drawer Bank & Individual up to 800mm**

42.85

45.56

48.25

50.93

53.62

56.32

59.02

61.71

69.78

80.55

91.32

Pt_Thermo_18mm

This is another example of how it works based on single scenario, which works well. (But I need a sum)



Type (Door, Drawer)	Height (mm)	Width (mm)	Style Group	Matrix Table	Height (LookUp)	Width (LookUp)	Sqm	Sqm $	Trade Price $	MadeCo COGs $	Sales Price $
Door	380	147	S1	PM_Style01_TextAsh	400	150	0.06	0	17.22	10.81	14.60
Drawer Individual	720	450	S3	PM_Style03_StudioStd	750	450	0.34	0	125.25	78.66	106.19

kweaver · May 10, 2021

Phew! So, it's combined (added) when 2 or 3 of them are filled in.

Formula with IF, AND, INDEX, MATCH and SUM returning N/A

OneChief

New Member

Excel Facts

kweaver

Well-known Member

OneChief

New Member

kweaver

Well-known Member

OneChief

New Member

kweaver

Well-known Member

kweaver

Well-known Member

kweaver

Well-known Member

OneChief

New Member

kweaver

Well-known Member

Similar threads

Forum statistics

Share this page

Formula with IF, AND, INDEX, MATCH and SUM returning N/A

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock