I’m trying to lookup prices from a sheet using index and match.
I ran into problems with my initial index and match, so started by doing 3 different index and matches for the different product types, which all work:
Column M - Powersafe Line Source & Line Drain (500A*/800A**)
=IF(F2="Set of 5",INDEX(Supplier!$C$5:$I$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$C$4:$I$4,0),MATCH($M2,Supplier!$C$3,0))*5,INDEX(Supplier!$C$5:$I$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$C$4:$I$4,0),MATCH($M2,Supplier!$C$3,0)))
Column M - Line Drain Lug
=IF(F380="Set of 5",INDEX(Supplier!$K$5:$M$13,MATCH($D380,Supplier!$A$5:$A$13,0),MATCH($C380,Supplier!$K$4:$M$4,0),MATCH($M380,Supplier!$K$3,0))*5,INDEX(Supplier!$K$5:$M$13,MATCH($D380,Supplier!$A$5:$A$13,0),MATCH($C380,Supplier!$K$4:$M$4,0),MATCH($M380,Supplier!$K$3,0)))
Column M – Line Source Lug
=IF(F542="Set of 5",INDEX(Supplier!$N$5:$P$13,MATCH($D542,Supplier!$A$5:$A$13,0),MATCH($C542,Supplier!$N$4:$P$4,0),MATCH($M542,Supplier!$N$3,0))*5,INDEX(Supplier!$N$5:$P$13,MATCH($D542,Supplier!$A$5:$A$13,0),MATCH($C542,Supplier!$N$4:$P$4,0),MATCH($M542,Supplier!$N$3,0)))
I want to combine these formulas so that there is one formula, rather than breaking out the data into sections with different formulas. What I’m trying to achieve is to use an IFERROR function to look at the first formula, and if there’s an error look at the second formula, and then if there’s an error look at the third formula.
I’ve come up with this:
=IFERROR(IF(F2="Set of 5",INDEX(Supplier!$C$5:$I$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$C$4:$I$4,0),MATCH($M2,Supplier!$C$3,0))*5,INDEX(Supplier!$C$5:$I$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$C$4:$I$4,0),MATCH($M2,Supplier!$C$3,0))),IFERROR(IF(F2="Set of 5",INDEX(Supplier!$K$5:$M$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$K$4:$M$4,0),MATCH($M2,Supplier!$K$3,0))*5,INDEX(Supplier!$K$5:$M$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C380,Supplier!$K$4:$M$4,0),MATCH($M2,Supplier!$K$3,0))),IFERROR(IF(F2="Set of 5",INDEX(Supplier!$N$5:$P$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$N$4:$P$4,0),MATCH($M2,Supplier!$N$3,0))*5,INDEX(Supplier!$N$5:$P$13,MATCH($D2,Supplier!$A$5:$A$13,0),MATCH($C2,Supplier!$N$4:$P$4,0),MATCH($M2,Supplier!$N$3,0))),"N/A")))
The data is sorted in order, i.e. rows 2-379 show the Powersafe Line Source & Line Drain (500A*/800A**), rows 380-541 show Line Drain Lug and rows 542-703 show Line Source Lug. The formula appears to working for the Powersafe Line Source & Line Drain (500A*/800A**), and the Line Source Lug, but the middle section for the Line Drain Lug is showing a lot of N/As and only working for the values which are supposed to be x 5.
Can anyone please help me?
