Hey all!
I'm currently using this formula, and it works perfectly to avoid a zero from being returned as a result. (section in bold) The problem being, this formula is copied across many cells, and is slowing down the workbook considerably.
=IF(INDEX(Main!$E:$E,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),))=0,"",INDEX(Main!$E:$E,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),)))
Since it's effectively doing 4 index matches per cell, over hundreds of cells, I get why it's taking so long. I need a way to make this process faster, and my first thought is to get rid of the bolded portion by somehow telling the formula to skip blank cells. When the formula encounters a blank cell, it returns a zero which is not good for my data. Hiding zeros in settings is not an option.
So if I get rid of the first part of the formula that hides the zero, I'm left with this: =INDEX(Main!$G:$G,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),))
In searching, I've found two sample formulas that may be able to ignore blanks in a index/match, but I can't seem to get them to work in my formula:
=INDEX(C2:C11,MODE(IF(C2:C11<>"",MATCH(C2:C11,C2:C11,0))))
=INDEX(C1:C4,MATCH(TRUE,IF(C1:C4<>"",IF(A1:A4=F1,B1:B4=F2)),0))
If someone could help me with a solution, I'd greatly appreciate it! This has been an ongoing project for the last week, and I'm running out of time to get this out the door. Thanks so much in advance!
I'm currently using this formula, and it works perfectly to avoid a zero from being returned as a result. (section in bold) The problem being, this formula is copied across many cells, and is slowing down the workbook considerably.
=IF(INDEX(Main!$E:$E,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),))=0,"",INDEX(Main!$E:$E,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),)))
Since it's effectively doing 4 index matches per cell, over hundreds of cells, I get why it's taking so long. I need a way to make this process faster, and my first thought is to get rid of the bolded portion by somehow telling the formula to skip blank cells. When the formula encounters a blank cell, it returns a zero which is not good for my data. Hiding zeros in settings is not an option.
So if I get rid of the first part of the formula that hides the zero, I'm left with this: =INDEX(Main!$G:$G,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),))
In searching, I've found two sample formulas that may be able to ignore blanks in a index/match, but I can't seem to get them to work in my formula:
=INDEX(C2:C11,MODE(IF(C2:C11<>"",MATCH(C2:C11,C2:C11,0))))
=INDEX(C1:C4,MATCH(TRUE,IF(C1:C4<>"",IF(A1:A4=F1,B1:B4=F2)),0))
If someone could help me with a solution, I'd greatly appreciate it! This has been an ongoing project for the last week, and I'm running out of time to get this out the door. Thanks so much in advance!
