Mar 05 2021 03:38 PM
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.
Mar 05 2021 03:47 PM
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.
Mar 06 2021 01:35 AM
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,"")