Forum Discussion
divide a cell value amongst one column that begins with and another column that begins with.
So this is a little bit of a complicated one.... Scenario: I have a budget sheet that is broken out by the first 2 numbers of a spec section. I have an import sheet for another program that i w...
=IF(MATCH(A1,$E$1:$E$3,0),VLOOKUP(A1,$E$1:$F$3,2,FALSE)/COUNTIF($A$1:$A$20,A1),"")Is this what you are looking for? Unfortunately I can't work with your file but in my understanding this should return the division part.
thats almost exactly what i have except my digits are longer than 2 so i'm trying to pull only the left 2 digits and its not liking that.... This is what i've gotten to, but it fails right at the end in the criteria part.....
=IF(AND((LEFT(I17,2)=VLOOKUP(LEFT(I17,2),'Section Costs'!$A$3:$K$38,1,FALSE)),(LEFT(D17,4)="INST")),VLOOKUP(LEFT($I17,2),'Section Costs'!$A$3:$K$38,11,FALSE)/(COUNTIF(LEFT($I$3:$I$493,2),(LEFT($I17,2)))),FALSE)
=IF(AND((LEFT(I17,2)=VLOOKUP(LEFT(I17,2),'Section Costs'!$A$3:$K$38,1,FALSE)),(LEFT(D17,4)="INST")),VLOOKUP(LEFT($I17,2),'Section Costs'!$A$3:$K$38,11,FALSE)/(COUNTIF(LEFT($I$3:$I$493,2),(LEFT($I17,2)))),FALSE)
=IFERROR(IF(MATCH(LEFT(A1,2),LEFT($E$1:$E$3,2),0),VLOOKUP(LEFT(A1,2),CHOOSE({1,2},LEFT($E$1:$E$3,2),$F$1:$F$3),2,0)/COUNTIF($A$1:$A$20,A1),""),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. This suggestion is with VLOOKUP and CHOOSE however it should be easier with INDEX and MATCH.
- my formula is working all the way up until I try and divide. I get the proper value returned, but its just when i try and divide at the end is when it goes wrong. its the countif portions that are not returning the values...