[et_pb_section fb_built=”1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IFNA in Google Sheets is a super simple function. It checks if a formula results in an error and returns a specific value if it does. This allows you to clear up errors from any spreadsheet making it easier to read.<\/span><\/p>\n In this article we will explore how the IFNA formula works and a few use cases where this function is invaluable.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The inner workings of this function are straightforward and it has two possible outcomes:<\/span><\/p>\n Its basic syntax looks like this:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFNA(value, [value_if_na_error])<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”common” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You could use this function any time a formula returns one of these errors. But there are some specific use cases that are especially common and useful.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n VLOOKUP is a function that returns #N\/A! errors every time it doesn\u2019t find a value it is looking for. And depending on the dataset you are working with this can happen a lot.<\/span><\/p>\n That\u2019s why it\u2019s a good idea to use both of these functions together. Just use IFNA to return blank values or a specific error message if VLOOKUP doesn\u2019t find anything.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =<\/span>IFNA<\/span>(<\/span>VLOOKUP<\/span>(<\/span>E3<\/span>,<\/span> <\/span>$A$3:$C$11<\/span>,<\/span> <\/span>2<\/span>,<\/span> <\/span>FALSE<\/span>)<\/span>,<\/span> <\/span>“Employee not found”<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Customizing this error message will give better feedback to you or anyone who reads the spreadsheet.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/06\/ifna-in-google-sheets_1.webp” alt=”ifna in google sheets 1″ title_text=”ifna-in-google-sheets_1″ align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”0″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n MATCH is another function that returns #N\/A! errors every time the function doesn’t find the value it’s looking for.<\/span><\/p>\n Again, to handle this you can use <\/span>IFNA<\/span>. This way, if <\/span>MATCH<\/span> doesn’t find the desired value, you can make it return 0 instead of an error.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =<\/span>IFNA<\/span>(<\/span>MATCH<\/span>(<\/span>E3<\/span>,<\/span> <\/span>$A$3:$A$11<\/span>,<\/span> <\/span>0<\/span>)<\/span>,<\/span> <\/span>0<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/06\/ifna-in-google-sheets_2.webp” alt=”ifna in google sheets 2″ title_text=”ifna-in-google-sheets_2″ align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”iferror” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n IFERROR is a lot like IFNA. The only difference is that it will detect any error, while IFNA will only look for one specific error. For the most part they are interchangeable, so the last formula we covered would work the same with IFERROR.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFERROR(MATCH(<\/span>A2<\/span>, <\/span>C2:C10<\/span>, <\/span>0<\/span>), <\/span>0<\/span>)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The only cases where these functions aren\u2019t the same is if you are trying to sniff out NA errors in particular.<\/span><\/p>\nSyntax of IFNA<\/span><\/h2>\n
\n
\n
Common uses cases of IFNA in Google Sheets<\/span><\/h2>\n
IFNA and VLOOKUP<\/span><\/h3>\n
IFNA then 0<\/span><\/h3>\n
IFNA vs IFERROR<\/span><\/h2>\n