Forum Discussion

ExcelApprentice96's avatar
ExcelApprentice96
Copper Contributor
Oct 22, 2026

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.

excel
Formulas and Functions

2 Replies

  • Terio's avatar
    Terio
    Brass Contributor
    Oct 24, 2026
    ExcelApprentice96 wrote:

    I'm trying to use the XLOOKUP formula

    Viewing your formula help us to help you.

    ExcelApprentice96 wrote:

    can't use the whole column without excel popping up an error message about there being a problem with my formula

    Be careful: the 2nd and 2rd parameters must be the same dimension; if you use the entire column as the search range, the return column must be an entire column and vice versa.

    Please post your attempt so that the correct solution can be suggested.

    Bye

  • VARSDigital's avatar
    VARSDigital
    Tin Contributor
    Oct 23, 2026

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