# IF BUT Formula Attempt - Nested IF Help?

1. ## IF BUT Formula Attempt - Nested IF Help?

In my spreadsheet, I am evaluating inventory counts from 2 data files and determining which value to keep in a “Status” column. My “Main Data” data is most likely to be up-to-date, but I need to pull from a “Backup Data” file when “Main Data” data is missing. This seems like a fairly straightforward IF statement to me. BUT if both my “Main Data” and “Backup Data” files list data and that data is different (see row 10 of attached), I need the word “CHECK” to appear in the Status column for someone to investigate. Intuitively, this sounds like an IF BUT formula, but I do not believe there is such a thing. The attached file includes my attempt to create the IF BUT I want, but displays “FALSE” when of the data files is blank instead of pulling from the other column.

Any help would be greatly appreciated!

2. ## Re: IF BUT Formula Attempt - Nested IF Help?

Try this in A2:

=IF(COUNTA(C2:D2)=1,MAX(C2:D2),IF(C2=D2,D2,"CHECK"))

3. ## Re: IF BUT Formula Attempt - Nested IF Help?

what happens if both C and D are blank

otherwise I would do the check first

=IF(AND(C2<>D2,ISNUMBER(C2),ISNUMBER(D2)),"CHECK", IF(ISNUMBER(C2),C2,D2))

4. ## Re: IF BUT Formula Attempt - Nested IF Help?

Etaf, your formula works great - I hadn't thought of both C + D being blank but ran into several records with this and rec'd a "0" output. I tried adding an additional IF(AND with ISBLANKS for C + D but was told I had entered too many arguments. Do you know a workaround? If both C + D are blank, I would want my status blank, as well (though I know I could technically replace the 0s in a 2nd formula but was trying to keep it to 1).

63falcondude, thanks for your help, too - yours would work except I need the Main Data count even if it is lower than the Backup Data count (though this formula will come in handy with other inventory sheets).

5. ## Re: IF BUT Formula Attempt - Nested IF Help?

Maybe this?

=IF(AND(C2="",D2=""),"",IF(AND(C2<>D2,ISNUMBER(C2),ISNUMBER(D2)),"CHECK", IF(ISNUMBER(C2),C2,D2)))

6. ## Re: IF BUT Formula Attempt - Nested IF Help?

Originally Posted by eNinjaInTraining
63falcondude, thanks for your help, too - yours would work except I need the Main Data count even if it is lower than the Backup Data count.
Did you try it? The MAX part only comes into effect when there is only one entry.

7. ## Re: IF BUT Formula Attempt - Nested IF Help?

=if( and( c2="",d2=""),"",if(and(c2<>d2,isnumber(c2),isnumber(d2)),"check", if(isnumber(c2),c2,d2))))

8. ## Re: IF BUT Formula Attempt - Nested IF Help?

Sorry for the late reply - I didn't realize I hadn't written back. You were right - your suggestion works. I got caught in my own understanding of the formula and thought the MAX may pull the larger number from the two programs if both records were populated, even if from the backup data. But that is what the "CHECK" is for, so it wouldn't.

9. ## Re: IF BUT Formula Attempt - Nested IF Help?

The above is for 63falcondude. Etaf and AliGW, your solutions work as well! Thank you all for your quick feedback - it's especially great that there are several methods I can lean to if I forget one. Apologies on the late feedback from my end.

10. ## Re: IF BUT Formula Attempt - Nested IF Help?

You're welcome. Glad we could help.

11. ## Re: IF BUT Formula Attempt - Nested IF Help?

There are currently 1 users browsing this thread. (0 members and 1 guests)