Forum Discussion
Combining If and Lookup Functions calculating some cells but not all
Hello,
I am having an odd issue with Excel that I cannot seem to find the answer to by searching Google.
I have a function that has several if functions, index/match, and lookup functions. The formula works for about 90% of the cells in the column but for some reason the other 10% only work if I click on the formula bar and hit enter. It then becomes a whack a mole game as previous ones will revert back to the incorrect number. Is there a simple way to get Excel to kick itself in gear and do all of the calculations correctly? The workbook has 15 tabs and each tab averages about 5500 rows so I do not have time to keep going into each incorrect cell to fix them.
I also attempted to split the formula into helper columns and then a simplified if function, but the same thing is happening.
Here is the larger formula:
=IF(AND(Q3>"",R3="N"),0,IF(AK3<O3,1,IF(AND($G3=0,ISERROR(LOOKUP(2,1/$P$2:P2,$P$2:P2))),1,IF(AND($G3=0,LOOKUP(2,1/$P$2:P2,$P$2:P2)<INDEX(Table!$M$4:$S$18,MATCH($C$1,Table!$M$4:$M$18,0),MATCH($O3,Table!$M$2:$S$2,0))),(LOOKUP(2,1/$P$2:P2,$P$2:P2)+1),IF(AND($G3=0,LOOKUP(2,1/$P$2:P2,$P$2:P2)>=INDEX(Table!$M$4:$S$18,MATCH($C$1,Table!$M$4:$M$18,0),MATCH($O3,Table!$M$2:$S$2,0))),1,0)))))
Here is the second attempt with additional columns:
=IF(AC3="Y",0,IF(AJ3="Y",1,IF(AK3="Y",LOOKUP(2,1/$P$2:$P2,$P$2:$P2)+1,"N/A")))
Any help will be greatly appreciated! I have spent most of the past two days trying to figure this out.
Is it the lookup function not working with the if functions that is causing the problems?
- Enter this formula in Column P, starting with P3:
=COUNTIF(Q$3:Q3,Q3)
Twifoo wrote:
Enter this formula in Column P, starting with P3:
=COUNTIF(Q$3:Q3,Q3)I am not sure how this formula helps me. If there is a value in Q then P should show 0 because the item has been counted. For the blanks in Q P should have a number between 1 and x depending on how many A, B, or C items there are per week for the location. When it hits x the next number needs to be 1 to start a new week. The formula I have works. Excel just does not want to calculate the cells 100% of the time so it throws off my week counts, and that is what I want to find a solution for.
- Your statements are contradicting so I can’t understand what you really want to achieve.
For me to even begin to help, I'd need an excel file with that in it. I cannot begin to disect that in text format.
But before you do that, if you put your data in an Excel table and made sure the column(s) all had 100% consistent formulas, that would go a long way to resolving this I suspect.
Attached is an example of the sheet. Column P is where I am having the issues. As a you scroll down you will see that numbers repeat where it should be either +1 of the previous number or 1 for a new week.
If you click on one of the cells that doubled up the previous number it will update to the correct number.
