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

Occasional Visitor

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 Replies


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

In sheet #2, cell D2:




Fill down.


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,"")