Question on Formula

New Contributor


I am hoping you can assist me with a formula.   

I have a spreadsheet with two sheets (tabs).  I need to check the data from Sheet 1, Column A and see if it is found in Sheet 2, column A and if so then return the value from sheet 2 column AM to sheet 1 column J. 

3 Replies


If you have Microsoft 365 or Office 2021:

In J2 on Sheet 1:

=XLOOKUP(A2, 'Sheet 2'!$A:$A, 'Sheet 2'!$AM:$AM, "")

 Replace Sheet 2 with the real name of that sheet.

If you have an older version:

=IFERROR(VLOOKUP(A2, 'Sheet 2'!$A:$AM, 39, FALSE), "")


Thank you @Hans Vogelaar .  It doesn't seem to be pulling the information from sheet 2 column AM back to sheet 1 column J.


Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.