Very strange formatting affecting match function

%3CLINGO-SUB%20id%3D%22lingo-sub-2673825%22%20slang%3D%22en-US%22%3EVery%20strange%20formatting%20affecting%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2673825%22%20slang%3D%22en-US%22%3E%3CP%3ETLDR%3A%20Look%20at%20the%20video%20link.%3C%2FP%3E%3CP%3EMy%20match%20function%20is%20acting%20very%20strange.%20The%20formula%20is%20below.%20Basically%2C%20it's%20supposed%20to%20ignore%20zeros%20and%20return%20the%20cell%20number%20of%20the%20first%20number%20in%20a%20range.%20However%2C%20it's%20still%20counting%20the%20zeros.%20These%20are%20some%20things%20I%20tried%20to%20make%20it%20not%20count%20the%20formulas%3CBR%20%2F%3E%3CBR%20%2F%3E-%20change%20the%20cells%20formating%20from%20general%20to%20number%3CBR%20%2F%3E-%20copy%20the%20cells%20and%20paste%20on%20the%20values%20(this%20deletes%20the%20formulas%20in%20the%20cells%20while%20keeping%20the%20values)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ENeither%20worked.%20The%20only%20way%20the%20match%20function%20works%20is%20if%20I%20go%20manually%20enter%20a%20zero%20in%20every%20cell.%20That's%20the%20only%20time%20the%20function%20will%20ignore%20the%20zeros.%20Of%20course%2C%20I%20have%20thousands%20of%20zeros%20it%20needs%20to%20ignore%2C%20so%20this%20solution%20isn't%20viable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat's%20going%20on%3F%20Please%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20video%20to%20show%20it%20in%20action%3A%20%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FVP_ai0o9Raw%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FVP_ai0o9Raw%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMATCH(TRUE%2CAE4%3AAP4%26gt%3B0%2C0)%3CBR%20%2F%3Eanother%20way%20to%20write%20it%20but%20still%20the%20same%20problem%3A%3CBR%20%2F%3E%3DMATCH(TRUE%2CINDEX(AE4%3AAP4%26gt%3B0%2C0)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2673825%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2673915%22%20slang%3D%22en-US%22%3ERe%3A%20Very%20strange%20formatting%20affecting%20match%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2673915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1134687%22%20target%3D%22_blank%22%3E%40AnEntree%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20check%20the%20cells%20with%20ISNUMER()%20then%20it%20will%20spill%20out%20FALSE%20because%20your%20formulas%20evaluate%20to%20%220%22%20and%20not%20to%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

TLDR: Look at the video link.

My match function is acting very strange. The formula is below. Basically, it's supposed to ignore zeros and return the cell number of the first number in a range. However, it's still counting the zeros. These are some things I tried to make it not count the formulas

- change the cells formating from general to number
- copy the cells and paste on the values (this deletes the formulas in the cells while keeping the values)

Neither worked. The only way the match function works is if I go manually enter a zero in every cell. That's the only time the function will ignore the zeros. Of course, I have thousands of zeros it needs to ignore, so this solution isn't viable.

 

What's going on? Please help.

 

Here's a video to show it in action: https://youtu.be/VP_ai0o9Raw

 

=MATCH(TRUE,AE4:AP4>0,0)
another way to write it but still the same problem:
=MATCH(TRUE,INDEX(AE4:AP4>0,0),0)

 

1 Reply

@AnEntree 

If you check the cells with ISNUMER() then it will spill out FALSE because your formulas evaluate to "0" and not to 0.