The VLOOKUP function returns #N/A when the searched value does not exist in the selected range of cells.
Could you please help us understand the scenario?
If someone else has completely understood this, please overwrite the answer.
What information/value is in your D2 cell?
From what cell and to what cell are you copying? (example: from A6 to A50)
What are the contents of the source and target cells before copying?
My guess (intuition only) is that you have some cells with spaces at the end of them. When you type, Excel truncates spaces, when you copy/paste a cell, it doesn’t.
Try looking at your lookup table. If it turns out that is the case, you can save a lot of typing by using a formula temporarily like C2 = TRIM(A2). You then copy column C, and use paste special, values, on column A. This removed leading or trailing spaces in all cells in one go. You can then remove column C