So does the partial file mean that it would search A1 in column M and should display the corresponding value in column N?

Would you run the formula on sheet1 or Sheet2.

I would suggest send an example sheet the way it looks (example: some data on Sheet1 and some on Sheet2, where would you run the formula and some comments)

Its a bit hard to interpret your query with your above posts.