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

Occasional Visitor

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

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

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

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.

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

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