Discussion IF formule with 0 in cell doesnt retrieve the correct matches in Excel
https://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112318#M88102
<P>Hi all,</P><P> </P><P>I have a file with numbers-> IF(B2>5;"ok";not"ok), Those numbers are retrieved via a Vlookup and in the Vlookup I added an IFNA --> if the corresponding name is not in the database, it should not give a N/A but a "0". But in the IF formule, if there is a 0 in the cell, the IF formula doesn't work properly as the IF gives for every 0 a "ok" which should be "not ok" because its <5. I am not able to find out what the problem is.</P><P> </P><P>I hope someone can help me.</P><P>Many Thanks.</P><P>Liz</P>Wed, 03 Feb 2021 09:36:49 GMTLiz1202021-02-03T09:36:49ZIF formule with 0 in cell doesnt retrieve the correct matches
https://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112318#M88102
<P>Hi all,</P><P> </P><P>I have a file with numbers-> IF(B2>5;"ok";not"ok), Those numbers are retrieved via a Vlookup and in the Vlookup I added an IFNA --> if the corresponding name is not in the database, it should not give a N/A but a "0". But in the IF formule, if there is a 0 in the cell, the IF formula doesn't work properly as the IF gives for every 0 a "ok" which should be "not ok" because its <5. I am not able to find out what the problem is.</P><P> </P><P>I hope someone can help me.</P><P>Many Thanks.</P><P>Liz</P>Wed, 03 Feb 2021 09:36:49 GMThttps://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112318#M88102Liz1202021-02-03T09:36:49ZRe: IF formule with 0 in cell doesnt retrieve the correct matches
https://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112451#M88110
<P><LI-USER uid="953231"></LI-USER> </P>
<P>In the IFNA formula, use 0 (without quotes) instead of "0".</P>
<P> </P>
<P>=IFNA(VLOOKUP(...),0)</P>Wed, 03 Feb 2021 10:58:09 GMThttps://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112451#M88110Hans Vogelaar2021-02-03T10:58:09ZRe: IF formule with 0 in cell doesnt retrieve the correct matches
https://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112607#M88128
It's working without the " indeed! Many thanks for your quick reply!Wed, 03 Feb 2021 13:43:41 GMThttps://techcommunity.microsoft.com/t5/excel/if-formule-with-0-in-cell-doesnt-retrieve-the-correct-matches/m-p/2112607#M88128Liz1202021-02-03T13:43:41Z