Forum Discussion
formulas
I'm trying to use the XLOOKUP formula and when trying I can use the 1 cell reference but can't use the whole column without excel popping up an error message about there being a problem with my formula. I even used the excel training video before coming here.
1 Reply
Whole columns are fine in XLOOKUP, so that popup usually means the column reference itself is written in a form Excel doesn't accept. This version works:
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")
Two things set that message off most often. First, a range with no end row, like A2:A (the way Google Sheets writes it). Excel wants either the whole column, A:A, or both ends, A2:A500. Second, a sheet name with a space in it needs apostrophes around it: 'Price List'!A:A, not Price List!A:A.
Keep the first argument as the one cell you're looking up (A2) and fill the formula down. The whole columns go in the second and third arguments. If the popup still shows, paste your exact formula here and we'll find the spot.
(Drafted with AI assistance; formulas tested before posting.)
