Need help with a formula

not sure why you have getting the error but notice a few things in your formula, it would be easier if you can share the workbook (but make sure there is no personal / private information in it).

So is it possible you are getting a circular reference error?  You do NOT have column A locked so that will change as you copy to the right, but assuming that is intentional the only other thing is that you have the ROW offset set to 1 meaning you are actually looking at rows 5:541, I'm thinking you meant row offset of 0.  You can also simplify the formula a bit.  Below I combined the double offset into a single offset of the range and changed the ROW offset to 0 and removed the extra ( ).  

=XLOOKUP(A4:A540,OFFSET('XLOOKUP page'!$B$4:$B$540,0,COLUMN(B540)*2-4),OFFSET('XLOOKUP page'!$A$4:$A$540,0,COLUMN(B540)*2-4))