Share via

XLOOKUP not finding true matches

Webb,R 1 Reputation point
2021-03-04T17:21:54.2+00:00

Is it possible for the xlookup formula to not find true matches when there actually are? We have a file with ~300k lines, when we type in our xlookup formula and double click the bottom right corner of the cell the formula is in, it appears to auto-complete the xlookup for the entire file, however, if you go to a cell with an #NA and hit enter, it will many times change it to a match (when the data is truly a match). So it's like the auto-"drag-down" feature isn't really working. We've even tried to manually drag it down, it still comes up with #NAs that are actually matches. Anyone have an idea why this might be happening? I've googled for about 2 hours, and not coming up with anything that's working. Could it be bc of the amount of data in any given cell? Like it results in a non-match bc it's not taking the time to check all of the data and compare if it's too much?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,660 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,551 Reputation points
    2021-03-05T06:11:06.96+00:00

    @Webb,R

    Could you please share us with a sample or a screenshot to show more detailed information?
    Such as the formula you use, the lookup value and the matched value in return array.

    It's recommend to create a new blank worksheet, and copy part of the data to this worksheet to test whether the amount of data affects the results of Xlookup function.
    In addintion, you may use other query functions (Such as Vlookup) to replace Xlookup function, check whether this issue could be reproduced.

    Please make sure the lookup value and values in the lookup arrary are spelled correctly and do not contain extra space.
    The type of lookup value is the same as the type of values in lookup arrary.
    Please also ensure the lookup array contains all required values, you may use "$" to make lookup array provided to the formula is correct.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    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.