how do I fix my excel sheet when my xlookup is giving me #N/A which I know for sure it is incorrect.

Hi @Shabana Hirani,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, there are many common reasons can cause Excel returns #N/A? error when running VLOOKUP formula:

1- Mismatch in data formatting

• The lookup value and values might look the same but may be actually different type (Number vs text).
• You can try using =ISTEXT() or =ISNUMBER() on both lookup value and value column to make sure they are in the same formatting.

2- Extra space or hidden characters

• Extra spaces when inputting value can also cause mismatches. You can try using =TRIM() or =CLEAN() in both lookup value to clean the data

3- Lookup value not in first column

• VLOOKUP only search in the first column for lookup range. If your customer names are in column B but your range starts from column A, it won't find it properly.

4- Matching confusion

• If you are using VLOOKUP formula with the fourth argument set to TRUE/omitted, it will return an approximate match which requires the lookup column must be sorted ascendingly. If the column is not sorted, the result of approximate match can return N/A or incorrect value.
• Therefore, make sure you are using exact match by setting the fourth argument to FALSE/0.

For further assistance, can you share us the sample data and also the formula you're having issue so we can take a look and understand the situation clearly to provide the most appropriate solution.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.