Copy from one sheet to another but if differences leave cells blank

[rrbailey89]

Hello, I have many sheets in my workbook.

The data between the sheets is almost identical. 

I am looking for a formula that will compare column A in sheet 2 to column A in sheet 1. If the data is the same then look in Column D of the same row as the matched data and insert the value from sheet 1 column D into sheet 2 column D. 

I have tried using this formula : =LOOKUP(A:A,'FY17 MFP'!A:A,'FY17 MFP'!D:D) 

however it seems to insert random data even if a value doesnt actually exist as oppsoed to leaving the field blank. 

Any help would be appreciated.

[#2]

@rrbailey89 

LOOKUP returns an approximate match. Use VLOOKUP instead (or XLOOKUP, if you have Microsoft 365).

In sheet #2, cell D2:

=IFERROR(VLOOKUP(A2, 'FY17 MFP'!$A:$D, 4, FALSE), "")

Fill down.

[#3]

@rrbailey89 

If with XLOOKUP()

=XLOOKUP(A:A,'FY17 MFP'!A:A,'FY17 MFP'!D:D,"")

formula will return entire column. However, it's better to use concrete range, static or dynamic, like

=XLOOKUP(A1:A1000,'FY17 MFP'!A1:A1000,'FY17 MFP'!D1:D1000,"")