XLOOKUP help

Hope Manning 0 Reputation points
2026-07-22T19:31:06.4833333+00:00

I've been having trouble with this excel sheet every time I use the XLOOKUP function. It gives me N/A. We were able to fix it once by changing the number format, but it continues to be an issue every time I add something new. I have tried pulling it form a pivot table, a regular table, and just some number typed in with no luck.

Microsoft 365 and Office | Excel | For business | Windows

2 answers

Sort by: Most helpful
  1. Ryan-N 17,000 Reputation points Microsoft External Staff Moderator
    2026-07-23T21:06:00.5633333+00:00

    Hi Hope,

    Thank you for your response.

    Please try using the following formula and check whether it resolves the issue:

    =XLOOKUP(A3&"",POs!I:I&"",POs!J:J)

    This formula forces the values to the same data format before performing the lookup, which helps avoid situations where the values appear identical but are stored as different data types.

    I hope this information is helpful.

    If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.

    I look forward to continuing the conversation.

     

    Was this answer helpful?


  2. Ryan-N 17,000 Reputation points Microsoft External Staff Moderator
    2026-07-22T20:04:26.3533333+00:00

    Hi Hope,

    Welcome to the Microsoft Q&A forum.

    The error code #N/A means that XLOOKUP cannot return a result because it cannot find a match within the lookup range, or the formula is no longer referring to the correct lookup range.

    Based on your description, you were previously able to resolve the issue by changing the data format to Number. However, the error continues to reappear whenever new data is added.

    This may indicate that the newly added data is being entered as Text rather than Number, causing XLOOKUP to be unable to find a matching result.

    To help you more effectively, could you please share the XLOOKUP formula you are using, along with a screenshot showing one of the values that is causing the issue?

    In the meantime, please refer to the following article:

    How to correct a #N/A error | Microsoft Support

    I look forward to hearing back from you.


    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.  

    Was this answer helpful?


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.