Forum Discussion

AidenTay's avatar
AidenTay
Copper Contributor
May 15, 2025

Need help with a formula

Hi all, I've got a problem with a formula I'm trying to use

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

 

The formula is supposed to do an XLOOKUP and when I drag the formula across it goes up in increments of 2 but however I try to do it I always get an error or the first column works and the subsequent ones dragged across don't work. Could anyone help please.

excel
Formulas and Functions

1 Reply

Sort By
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    May 16, 2025

    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))

     

Resources

OSZAR »