Highlighted
New Contributor

# 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?

5 Replies
Highlighted

# Re: Combining If and Lookup Functions calculating some cells but not all

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.

Highlighted

# Re: Combining If and Lookup Functions calculating some cells but not all

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.

Highlighted

# Re: Combining If and Lookup Functions calculating some cells but not all

Enter this formula in Column P, starting with P3:
=COUNTIF(Q\$3:Q3,Q3)
Highlighted

# Re: Combining If and Lookup Functions calculating some cells but not all

@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.

Highlighted

# Re: Combining If and Lookup Functions calculating some cells but not all

Your statements are contradicting so I can’t understand what you really want to achieve.