Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 464K Members
- 11.4K Online
- 560K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Combining If and Lookup Functions calculating some cells but not all

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Combining If and Lookup Functions calculating some cells but not all

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2019 01:43 PM

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?

Labels:

5 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2019 05:14 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2019 06:14 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2019 10:03 PM

Enter this formula in Column P, starting with P3:

=COUNTIF(Q$3:Q3,Q3)

=COUNTIF(Q$3:Q3,Q3)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2019 07:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2019 06:04 PM

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

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
35.3K
Views

7 Likes

35 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
31.1K
Views

14 Likes

14 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
181K
Views

8 Likes

29 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16.5K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft