You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
GLITCH using INDEX-MATCH formula
- Thread starter Felix
- Start date
NARAYANK991
Excel Ninja
Hi ,
Your formula is :
=IF($B33="",0,IF($G$2=$N$1,INDEX($R$60:$R$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$2,INDEX($S$60:$S$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$3,INDEX($T$60:$T$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$4,INDEX($U$60:$U$273,MATCH(C33,$P$59:$P$273,0)),IF($G$2=$N$5,INDEX($V$60:$V$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$6,INDEX($W$60:$W$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$7,INDEX($X$60:$X$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$8,INDEX($Y$60:$Y284,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C33,$P$274:$P$438,0)),IF($G$2=$P$2,INDEX($R$440:$R$616,MATCH(C33,$P$440:$P$616,0)),IF($G$2=$P$3,INDEX($R$618:$R$792,MATCH(C33,$P$618:$P$792,0)),IF($G$2=$P$4,INDEX($R$794:$R$1159,MATCH(C33,$P$794:$P$1159,0)),IF($G$2=$P$5,INDEX($R$1161:$R$1324,MATCH(C33,$P$1161:$P$1324,0)),0))))))))))))))
The relevant section is highlighted.
If the first parameter to the INDEX function is the range $U$60:$U$273 , then the lookup range , the second parameter to the MATCH function should be $P$60:$P$273.
Change this.
You should also correct the following sections :
IF($G$2=$N$8,INDEX($Y$60:$Y284,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C33,$P$274:$P$438,0))
In any INDEX + MATCH combination , the range to the INDEX function and the range to the MATCH function should have identically sized ranges. What this means is that the number of cells in both ranges should be the same ; this if the INDEX function is ranging over 225 cells , from row 60 through row 284 , then the MATCH function should also range over 225 cells ; these 225 cells can be anywhere in the worksheet depending on how your data is laid out , but there should be 225 cells in the MATCH function's lookup range.
Narayan
Your formula is :
=IF($B33="",0,IF($G$2=$N$1,INDEX($R$60:$R$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$2,INDEX($S$60:$S$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$3,INDEX($T$60:$T$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$4,INDEX($U$60:$U$273,MATCH(C33,$P$59:$P$273,0)),IF($G$2=$N$5,INDEX($V$60:$V$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$6,INDEX($W$60:$W$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$7,INDEX($X$60:$X$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$8,INDEX($Y$60:$Y284,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C33,$P$274:$P$438,0)),IF($G$2=$P$2,INDEX($R$440:$R$616,MATCH(C33,$P$440:$P$616,0)),IF($G$2=$P$3,INDEX($R$618:$R$792,MATCH(C33,$P$618:$P$792,0)),IF($G$2=$P$4,INDEX($R$794:$R$1159,MATCH(C33,$P$794:$P$1159,0)),IF($G$2=$P$5,INDEX($R$1161:$R$1324,MATCH(C33,$P$1161:$P$1324,0)),0))))))))))))))
The relevant section is highlighted.
If the first parameter to the INDEX function is the range $U$60:$U$273 , then the lookup range , the second parameter to the MATCH function should be $P$60:$P$273.
Change this.
You should also correct the following sections :
IF($G$2=$N$8,INDEX($Y$60:$Y284,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C33,$P$274:$P$438,0))
In any INDEX + MATCH combination , the range to the INDEX function and the range to the MATCH function should have identically sized ranges. What this means is that the number of cells in both ranges should be the same ; this if the INDEX function is ranging over 225 cells , from row 60 through row 284 , then the MATCH function should also range over 225 cells ; these 225 cells can be anywhere in the worksheet depending on how your data is laid out , but there should be 225 cells in the MATCH function's lookup range.
Narayan
Thank you very much, that's exactly what was hapening. This happened to me because I copied and paste, but now I learned something new, and I hope someone else did too.Hi ,
Your formula is :
=IF($B33="",0,IF($G$2=$N$1,INDEX($R$60:$R$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$2,INDEX($S$60:$S$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$3,INDEX($T$60:$T$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$4,INDEX($U$60:$U$273,MATCH(C33,$P$59:$P$273,0)),IF($G$2=$N$5,INDEX($V$60:$V$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$6,INDEX($W$60:$W$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$7,INDEX($X$60:$X$273,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$N$8,INDEX($Y$60:$Y284,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C33,$P$274:$P$438,0)),IF($G$2=$P$2,INDEX($R$440:$R$616,MATCH(C33,$P$440:$P$616,0)),IF($G$2=$P$3,INDEX($R$618:$R$792,MATCH(C33,$P$618:$P$792,0)),IF($G$2=$P$4,INDEX($R$794:$R$1159,MATCH(C33,$P$794:$P$1159,0)),IF($G$2=$P$5,INDEX($R$1161:$R$1324,MATCH(C33,$P$1161:$P$1324,0)),0))))))))))))))
The relevant section is highlighted.
If the first parameter to the INDEX function is the range $U$60:$U$273 , then the lookup range , the second parameter to the MATCH function should be $P$60:$P$273.
Change this.
You should also correct the following sections :
IF($G$2=$N$8,INDEX($Y$60:$Y284,MATCH(C33,$P$60:$P$273,0)),IF($G$2=$P$1,INDEX($R$277:$R$438,MATCH(C33,$P$274:$P$438,0))
In any INDEX + MATCH combination , the range to the INDEX function and the range to the MATCH function should have identically sized ranges. What this means is that the number of cells in both ranges should be the same ; this if the INDEX function is ranging over 225 cells , from row 60 through row 284 , then the MATCH function should also range over 225 cells ; these 225 cells can be anywhere in the worksheet depending on how your data is laid out , but there should be 225 cells in the MATCH function's lookup range.
Narayan
Thanks, but NARAYANK found the issue. Thanks for your time and effort.Hi:
Is this what you are looking for?
=IFERROR(INDEX($R$59:$Y$273,MATCH(C22,$P$59:$P$273,0),MATCH($G$2,$R$59:$Y$59,0)),0)
Formula in coloured cells in the attached file.
Thanks