Software⏱️ 3 min read📅 2026-06-19

How to Fix: Excel #NA error returning TRUE

Excel formula error returning TRUE due to #NA error

Quick Answer: Use the IFERROR function to handle the #NA error and return a specific value instead of TRUE.

📋 Table of Contents

  1. ⚠️ Common Causes
  2. 🚀 How to Resolve This Issue
  3. 🎯 Final Words

The Excel #NA error returning TRUE issue affects users who rely on formulas to check for duplicates in unsorted data. This can be frustrating, especially when using VLOOKUP functions to match values.

Using the 'Evaluate Formula' button can provide insight into the error, but it may not always lead to a clear solution. In this guide, we'll explore the root causes of this issue and provide two primary fix methods to resolve the problem.

⚠️ Common Causes

  • The first main reason for the #NA error returning TRUE is that the VLOOKUP function in Excel returns an error when it can't find a match. In this case, the data in column B is not sorted alphabetically, causing the VLOOKUP to return an error instead of a FALSE value.
  • An alternative reason for this issue is that the EXACT function in Excel treats uppercase and lowercase letters as distinct characters. If the data in column B contains mixed case, the EXACT function may return TRUE even if there's no match in the VLOOKUP result.

🚀 How to Resolve This Issue

Sorting Data Before Using VLOOKUP

  1. Step 1: To fix this issue, sort the data in column B before using the VLOOKUP function. This will ensure that the data is in alphabetical order, allowing the VLOOKUP to return a FALSE value when there's no match.
  2. Step 2: Select the entire range of data in column B and go to the Data tab in the ribbon. Click on 'Sort & Filter' and select 'Sort A to Z'. Repeat this step for any other columns used in the formula.
  3. Step 3: After sorting the data, re-enter the original formula with the VLOOKUP function, and it should now return a FALSE value when there's no match.

Using the IFERROR Function

  1. Step 1: Alternatively, you can use the IFERROR function to handle the error returned by the VLOOKUP. This will allow you to specify a custom value or behavior when an error occurs.
  2. Step 2: Replace the original formula with: =IFERROR(EXACT(B2,VLOOKUP(B2,$F$1:$F$76,1,0)),FALSE)
  3. Step 3: This formula uses the IFERROR function to check if the VLOOKUP returns an error. If it does, the IFERROR function will return a FALSE value instead of the original #NA error.

🎯 Final Words

In conclusion, the Excel #NA error returning TRUE issue can be resolved by sorting the data before using VLOOKUP or by using the IFERROR function to handle errors. By following these steps, you should be able to resolve this issue and improve the accuracy of your formulas.

Did this fix your problem?

If not, try searching for specific error codes.

🔍 Search Error Database

❓ Frequently Asked Questions

🛠️ Related Fixes

How to Fix: Pc crashes shortly after launching game (rainbow

Fix Pc crashes shortly after launching game (rainbow six siege). Compl

How to Fix: Installing an APK on a locked down phone

Installing an APK on a locked down phone: Try using a rooted device, e

How to Fix: New PC build- no signal and no clue

Fix New PC build- no signal and no clue. Complete troubleshooting guid