Pasting data into lookup range returns empty cells

Occasional Visitor

I am attempting to retrieve data from a webpage that does not provide an export feature and use that data in a spreadsheet. The webpage containing the data displays open purchase orders with status update information in a table-like configuration such that I can copy and paste it into a sheet which I label "raw".


Then in a separate sheet labeled "Status"  I list all open customer invoices in column 1 and the purchase order numbers attached to them in column 2. I then use xlookup to retrieve the status information for the purchase order from the raw table into columns 3 and 4. This works flawlessly the first time. But when I paste updated data into the raw table, suddenly all my xlookups return empty cells. I have tried using paste special, paste values, basic paste, and match destination formatting paste all to no avail. I tried re-entering the formula but it did nothing. I used calculate sheet and calculate now... Still nothing. I checked to make sure that relative references hasn't messed with me but they still pointed to the correct range. The only way I found to solve it was to delete the columns with the xlookups and re-build them from scratch and then they worked fine. But this is highly impractical as I am working with several thousand lines of data that I need to see updated information on daily and I need to be able to sort it.


I also have another table where I import merchandise data into raw tables from several external sources on separate sheets for different categories and then compile them into a larger, more complete sheet using xlookup inside nested ifs to import the data from the correct sheet based on the item's category. Again, it works super until I go to import updated data, at which point, I find myself having to rebuild the spreadsheet again. Again, this information is such that I need to update it regularly, and without a reliable data connection that I can maintain with the website (third party) I'm not sure how else to import my data without referencing the table I'm provided. 


Can someone help me sort out what I'm doing wrong? Why does the xlookup stop working when I update the information in the lookup range? 

1 Reply

Hi @Sinistersam 


is it possible that you provide a sample file (all sensitive data removed) which shows the situation after you have pasted new data in your raw data sheet?

This way, we could have a closer look on the formulas and references and possibly provide some advices.