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

O

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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"
1620685194795.png


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.
1620685471627.png

1620685511459.png

=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)
1620686053890.png

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.
 
Sort by date Sort by votes
kweaver said:
What do you think?

May102021.xlsm
ABCDEFGHIJKLMNOPQRS
1F Hgt 1F Hgt 2F Hgt 3F Hgt 4F Hgt 5F Hgt 6F Wid 1F Wid 2F Wid 3H1 (lookup)W1 (lookup)H2W2H3W3PM_Style01_TextAsh
23901474001500000Door17,22
34501976003000000Door26.62
Sheet9
Cell Formulas
RangeFormula
L2:L3L2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=B2,0),1),0)
M2:M3M2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=H2,0)),0)
N2:N3N2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=C2,0),1),0)
O2:O3O2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=I2,0)),0)
P2:P3P2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=D2,0),1),0)
Q2:Q3Q2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=J2,0)),0)
S2:S3S2=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)),"")
Click to expand...
No I dont think so..
This is How it should answer.
BID_Pricing.xlsm
BCDEFGHIJKLMNOPQRS
1Front Height 1Front Height 2Front Height 3Front Height 4Front Height 5Front Height 6Front 1 WidthFront 2 WidthFront 3 WidthH1 (Lookup)W1 (Lookup)H2 (Lookup)W2 (Lookup)H3 (Lookup)W3 (Lookup)PM_Style01_TextAsh
23801474001500000Door#N/A
338038019719740030040030000Door#N/A
4380380380247247247400300400300400300Door#REF!
5380297Answers Should be
638034717.22
738039743.64
838044765.46
9380497
WallCabinets
Cell Formulas
RangeFormula
L2:L4L2=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")))))))))))))
M2M2=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:N4N2=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")))))))))))))
O2O2=IF(I2<=0,0,IF(I2<=150,150,IF(I2<=300,300,IF(I2<=350,350,IF(I2<=400,400,IF(I2<=450,450,IF(I2<=500,500,IF(I2<=550,550,IF(I2<=600,600,IF(I2<=650,650,IF(I2<=800,800,IF(I2<=1000,1000,IF(I2<=1200,1200,"ERROR")))))))))))))
P2:P4P2=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")))))))))))))
Q2Q2=IF(J2<=0,0,IF(J2<=150,150,IF(J2<=300,300,IF(J2<=350,350,IF(J2<=400,400,IF(J2<=450,450,IF(J2<=500,500,IF(J2<=550,550,IF(J2<=600,600,IF(J2<=650,650,IF(J2<=800,800,IF(J2<=1000,1000,IF(J2<=1200,1200,"ERROR")))))))))))))
M3:M4M3=IF(H3<=0,0,IF(H3<=150,150,IF(H3<=300,300,IF(H3<=350,350,IF(H3<=400,400,IF(H3<=450,450,IF(H3<=500,500,IF(H3<=550,550,IF(H3<=600,600,IF(H3<=650,650,IF(H3<=800,800,IF(H3<=1000,1000,IF(H3<=1200,1200,"0")))))))))))))
O3:O4O3=IF(I3<=0,0,IF(I3<=150,150,IF(I3<=300,300,IF(I3<=350,350,IF(I3<=400,400,IF(I3<=450,450,IF(I3<=500,500,IF(I3<=550,550,IF(I3<=600,600,IF(I3<=650,650,IF(I3<=800,800,IF(I3<=1000,1000,IF(I3<=1200,1200,"0")))))))))))))
Q3:Q4Q3=IF(J3<=0,0,IF(J3<=150,150,IF(J3<=300,300,IF(J3<=350,350,IF(J3<=400,400,IF(J3<=450,450,IF(J3<=500,500,IF(J3<=550,550,IF(J3<=600,600,IF(J3<=650,650,IF(J3<=800,800,IF(J3<=1000,1000,IF(J3<=1200,1200,"0")))))))))))))
S1:AN1S1=TRANSPOSE(Pt_PriceGroupT[Price Groups (Polytec)])
S2:S4S2=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)))))
S6S6=IF(AND($S$1="PM_Style01_TextAsh",R2="Door"),INDEX(PM_Style01_TextAsh,MATCH(L2,PM_Style01_TextAshH),MATCH(M2,PM_Style01_TextAshW)))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
PM_Style01_TextAsh=Pt_Thermo_18mm!$C$8:$N$19S2:S4, S6
PM_Style01_TextAshH=Pt_Thermo_18mm!$B$8:$B$19S2:S4, S6
PM_Style01_TextAshW=Pt_Thermo_18mm!$C$7:$N$7S2:S4, S6
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
I think you should be able to build on this:

May102021.xlsm
ABCDEFGHIJKLMNOPQRS
1F Hgt 1F Hgt 2F Hgt 3F Hgt 4F Hgt 5F Hgt 6F Wid 1F Wid 2F Wid 3H1 (lookup)W1 (lookup)H2W2H3W3PM_Style01_TextAsh
23901474001500000Door17.22
340040015015040015040015000Door34.44
4400400400150150150400150400150400150Door51.66
Sheet9
Cell Formulas
RangeFormula
L2:L4L2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=B2,0),1),0)
M2:M4M2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=H2,0)),0)
N2:N4N2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=C2,0),1),0)
O2:O4O2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=I2,0)),0)
P2:P4P2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=D2,0),1),0)
Q2:Q4Q2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=J2,0)),0)
S2:S4S2=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)))+(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=$C2,0),MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=$I2,0)))+(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=$D2,0),MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=$J2,0))),"")


And with new info from you:

May102021.xlsm
BCDEFGHIJKLMNOPQRS
1F Hgt 1F Hgt 2F Hgt 3F Hgt 4F Hgt 5F Hgt 6F Wid 1F Wid 2F Wid 3H1 (lookup)W1 (lookup)H2W2H3W3PM_Style01_TextAsh
23901474001500000Door17.22
340040015015040015040015000Door34.44
4400400400150150150400150400150400150Door51.66
5380380380247247247400300400300400300Door65.46
Sheet9
Cell Formulas
RangeFormula
L2:L5L2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=B2,0),1),0)
M2:M5M2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=H2,0)),0)
N2:N5N2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=C2,0),1),0)
O2:O5O2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=I2,0)),0)
P2:P5P2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=D2,0),1),0)
Q2:Q5Q2=IFERROR(INDEX(Pt_Thermo_18mm!$C$8:$I$20,1,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=J2,0)),0)
S2:S5S2=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)))+(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=$C2,0),MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=$I2,0)))+(INDEX(Pt_Thermo_18mm!$C$8:$I$20,MATCH(TRUE,Pt_Thermo_18mm!$C$8:$C$20>=$D2,0),MATCH(TRUE,Pt_Thermo_18mm!$C$8:$I$8>=$J2,0))),"")
 
Upvote 0
I don't think this is the solution.
Cells/Columns ranged A through J is imported data which changes and updates.

However I have found that this works. Do you see any problems with it? Or a cleaner solution?
BID_Pricing.xlsm
ABCDEFGHIJKLMNOPQRS
1Cabinet CodeFront Height 1Front Height 2Front Height 3Front Height 4Front Height 5Front Height 6Front 1 WidthFront 2 WidthFront 3 WidthH1 (Lookup)W1 (Lookup)H2 (Lookup)W2 (Lookup)H3 (Lookup)W3 (Lookup)PM_Style01_TextAsh
2WS-150-L3801474001500000Door17.22
3WS-200-L38038019719740030040030000Door43.64
4WS-250-L380380380247247247400300400300400300Door65.46
WallCabinets
Cell Formulas
RangeFormula
L2:L4L2=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")))))))))))))
M2M2=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:N4N2=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")))))))))))))
O2O2=IF(I2<=0,0,IF(I2<=150,150,IF(I2<=300,300,IF(I2<=350,350,IF(I2<=400,400,IF(I2<=450,450,IF(I2<=500,500,IF(I2<=550,550,IF(I2<=600,600,IF(I2<=650,650,IF(I2<=800,800,IF(I2<=1000,1000,IF(I2<=1200,1200,"ERROR")))))))))))))
P2:P4P2=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")))))))))))))
Q2Q2=IF(J2<=0,0,IF(J2<=150,150,IF(J2<=300,300,IF(J2<=350,350,IF(J2<=400,400,IF(J2<=450,450,IF(J2<=500,500,IF(J2<=550,550,IF(J2<=600,600,IF(J2<=650,650,IF(J2<=800,800,IF(J2<=1000,1000,IF(J2<=1200,1200,"ERROR")))))))))))))
M3:M4M3=IF(H3<=0,0,IF(H3<=150,150,IF(H3<=300,300,IF(H3<=350,350,IF(H3<=400,400,IF(H3<=450,450,IF(H3<=500,500,IF(H3<=550,550,IF(H3<=600,600,IF(H3<=650,650,IF(H3<=800,800,IF(H3<=1000,1000,IF(H3<=1200,1200,"0")))))))))))))
O3:O4O3=IF(I3<=0,0,IF(I3<=150,150,IF(I3<=300,300,IF(I3<=350,350,IF(I3<=400,400,IF(I3<=450,450,IF(I3<=500,500,IF(I3<=550,550,IF(I3<=600,600,IF(I3<=650,650,IF(I3<=800,800,IF(I3<=1000,1000,IF(I3<=1200,1200,"0")))))))))))))
Q3:Q4Q3=IF(J3<=0,0,IF(J3<=150,150,IF(J3<=300,300,IF(J3<=350,350,IF(J3<=400,400,IF(J3<=450,450,IF(J3<=500,500,IF(J3<=550,550,IF(J3<=600,600,IF(J3<=650,650,IF(J3<=800,800,IF(J3<=1000,1000,IF(J3<=1200,1200,"0")))))))))))))
S1:AN1S1=TRANSPOSE(Pt_PriceGroupT[Price Groups (Polytec)])
S2:S4S2=IFERROR(IF(AND($S$1="PM_Style01_TextAsh",R2="Door"),INDEX(PM_Style01_TextAsh,MATCH(L2,PM_Style01_TextAshH),MATCH(M2,PM_Style01_TextAshW)),0),0)+ IFERROR(IF(AND($S$1="PM_Style01_TextAsh",R2="Door"),INDEX(PM_Style01_TextAsh,MATCH(N2,PM_Style01_TextAshH),MATCH(O2,PM_Style01_TextAshW)),0),0)+ IFERROR(IF(AND($S$1="PM_Style01_TextAsh",R2="Door"),INDEX(PM_Style01_TextAsh,MATCH(P2,PM_Style01_TextAshH),MATCH(Q2,PM_Style01_TextAshW)),0),0)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
PM_Style01_TextAsh=Pt_Thermo_18mm!$C$8:$N$19S2:S4
PM_Style01_TextAshH=Pt_Thermo_18mm!$B$8:$B$19S2:S4
PM_Style01_TextAshW=Pt_Thermo_18mm!$C$7:$N$7S2:S4
 
Upvote 0
Solution
I put your numbers back into my sheet and got the same answers in S: 17.22, 43.64, and 65.46
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
find product of 2 cells and a match index in a row, then sum the result
Replies
2
Views
746
radian89
R
A
  • Question Question
Formula Help!: Index/Match to return a range to use in Sumpr
Replies
6
Views
7K
Aladin Akyurek
Aladin Akyurek

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top