Hi there, we’re Harisystems

"Unlock your potential and soar to new heights with our exclusive online courses! Ignite your passion, acquire valuable skills, and embrace limitless possibilities. Don't miss out on our limited-time sale - invest in yourself today and embark on a journey of personal and professional growth. Enroll now and shape your future with knowledge that lasts a lifetime!".

For corporate trainings, projects, and real world experience reach us. We believe that education should be accessible to all, regardless of geographical location or background.

1
1

Microsoft Excel XLOOKUP: Handling #NA Formulas with Examples

Microsoft Excel's XLOOKUP function is a powerful tool for searching and retrieving data from a table. In some cases, when using XLOOKUP, you may encounter the #NA error. This error occurs when the lookup value is not found in the lookup array. In this guide, we will explore different techniques for handling the #NA error in XLOOKUP formulas, along with examples to demonstrate their application.

Using IFERROR Function

The IFERROR function is a useful tool for handling errors in Excel formulas. You can use it to catch the #NA error and return a custom message or alternative value. Here's an example:

    =IFERROR(XLOOKUP(lookup_value, lookup_array, return_array), "Not Found")
  

In this formula, if the XLOOKUP function returns the #NA error, the IFERROR function will catch it and return the specified message "Not Found" instead.

Using ISNA Function

The ISNA function can be used in combination with XLOOKUP to check if the result is an #NA error. Here's an example:

    =IF(ISNA(XLOOKUP(lookup_value, lookup_array, return_array)), "Not Found", XLOOKUP(lookup_value, lookup_array, return_array))
  

In this formula, the ISNA function checks if the result of the XLOOKUP function is an #NA error. If it is, it returns the specified message "Not Found". Otherwise, it returns the actual result from the XLOOKUP function.

Using IF and XLOOKUP

Another approach is to use the IF function in combination with XLOOKUP to handle the #NA error. Here's an example:

    =IF(XLOOKUP(lookup_value, lookup_array, return_array)="#NA", "Not Found", XLOOKUP(lookup_value, lookup_array, return_array))
  

In this formula, the IF function checks if the result of the XLOOKUP function is equal to the #NA error. If it is, it returns the specified message "Not Found". Otherwise, it returns the actual result from the XLOOKUP function.

Conclusion

Encountering the #NA error in XLOOKUP formulas is common when the lookup value is not found in the lookup array. However, by using techniques like IFERROR, ISNA, or IF functions, you can handle the #NA error and provide a customized message or alternative value. Experiment with these approaches and choose the one that best fits your specific requirements. With proper error handling, you can ensure your Excel worksheets provide accurate and meaningful results.

4.5L

Learners

20+

Instructors

50+

Courses

6.0L

Course enrollments

4.5/5.0 5(Based on 4265 ratings)

Future Trending Courses

When selecting, a course, Here are a few areas that are expected to be in demand in the future:.

Beginner

The Python Course: Absolute Beginners for strong Fundamentals

By: Sekhar Metla
4.5 (13,245)
Intermediate

JavaScript Masterclass for Beginner to Expert: Bootcamp

By: Sekhar Metla
4.5 (9,300)
Intermediate

Python Coding Intermediate: OOPs, Classes, and Methods

By: Sekhar Metla
(11,145)
Intermediate

Microsoft: SQL Server Bootcamp 2023: Go from Zero to Hero

By: Sekhar Metla
4.5 (7,700)
Excel course

Future Learning for all

If you’re passionate and ready to dive in, we’d love to join 1:1 classes for you. We’re committed to support our learners and professionals their development and well-being.

View Courses

Most Popular Course topics

These are the most popular course topics among Software Courses for learners