VLOOKUP Formula not calculating when copied down the rest of the column

Hi there,

I am quite well versed in Excel but am having a problem that I hoped someone could help with.

I have a spreadsheet that contains email addresses and unique IDs from our ESP, i have as separate sheet containing all of the email addresses and CRM IDs in our CRM system. The purpose of this vlookup is to figure out which email addresses are in our CRM system, and which are not. The vlookup is working no problem in terms of identifying an email address and returning the CRM ID where one exists and N/A when it does not find an exact match. However, the issue is that I have 300,000 records to check. When i drag the formula to copy it down the column it works, but when i select the whole column and paste, it just returns the same value as the original cell.

I have tried 2 things :
- I have checked that the formula calculation is on automatic, i even changed it to manual and pressed calculate now, with nothing happening.
- I have also tried changing the lookup to the exact cell in column A, rather than A:A. This also made no difference.

I have a feeling it is because of the large data set but am unsure and need to resolve this so I know which addresses to add in to the system and avoid duplication.

Hopefully someone can help?

Many thanks!

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

What 'exactly' are your steps here ?

but when i select the whole column and paste

It's probably not wise to try to do 'The WHOLE column'

If dragging it down by hand works, and you really only want to get the formula to 300,000 cells
Try just double clicking on the little square at the bottom right corner of the cell with the formula.

I had anchored the array and after checking back, it was a case of the amount of records which was causing the problems. I have since broken it down in to buckets of 30 - 50k and it is working as expected. Thank you all for the help.