SOLVED
Home

Why doesn't my formula work?

%3CLINGO-SUB%20id%3D%22lingo-sub-713259%22%20slang%3D%22en-US%22%3EWhy%20doesn't%20my%20formula%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713259%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20see%20the%20attached%20document.%20I've%20used%20a%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISNUMBER(SEARCH(%22Jan%22%2CC3))%2CD3%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20cell%20G3%2C%20why%20is'nt%20this%20picking%20up%20that%20there%20is%20'Jan'%20in%20C3%20and%20thus%20producing%20the%20value%20in%20D3%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Bailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-713259%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713274%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20my%20formula%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20C3%20you%20have%20a%20number%2C%20it's%20only%20formatting%20on%20the%20top.%20Use%3C%2FP%3E%0A%3CPRE%3E%3DIF(TEXT(C3%2C%22mmm%22)%3D%22Jan%22%2CD3%2C0)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713295%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20my%20formula%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713295%22%20slang%3D%22en-US%22%3ESEARCH%20is%20case%20sensitive%2C%20is%20works%20for%20me%20if%20I%20use%20FIND%20like%20so%3A%3CBR%20%2F%3E%3DIF(ISERROR(FIND(%24G2%2CC%243))%2C0%2C%24D3)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-713320%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20doesn't%20my%20formula%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713320%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20that%20the%20expected%20result%20is%20a%20number%2C%20you%20may%20also%20use%20this%20Boolean%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D(TEXT(%24C3%2C%22mmm%22)%3DG%242)*%24D3%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bailey100
Contributor

Hi, see the attached document. I've used a formula:

 

=IF(ISNUMBER(SEARCH("Jan",C3)),D3,0)

 

for cell G3, why is'nt this picking up that there is 'Jan' in C3 and thus producing the value in D3?

 

Thanks, Bailey100

3 Replies
Solution

@Bailey100 

 

In C3 you have a number, it's only formatting on the top. Use

=IF(TEXT(C3,"mmm")="Jan",D3,0)
SEARCH is case sensitive, is works for me if I use FIND like so:
=IF(ISERROR(FIND($G2,C$3)),0,$D3)

@Bailey100 

Given that the expected result is a number, you may also use this Boolean formula: 

=(TEXT($C3,"mmm")=G$2)*$D3