Forum Discussion

xd40c's avatar
xd40c
Copper Contributor
Dec 16, 2023
Solved

Xlookup issue

I'm trying to lookup a value from 4 columns (columns 3-6) in a 6 column table.  Then return all 6 columns.   Here are the column headers:   CADI_NUMBERS CADI_ITEM_NUMBERS TUFFALOY_NUMBERS C...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Dec 16, 2023

    xd40c 

    The second argument of XLOOKUP must be a single column (or row). You cannot specify a multi-column range.

    Use (for example)

     

    =INDEX(Table5,MATCH(TRUE,(Table5[TUFFALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CMW_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[TIPALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CENTERLINE_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)>0,0),0)

     

    or

     

    =FILTER(Table5,(Table5[TUFFALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CMW_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[TIPALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CENTERLINE_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)>0)

     

HansVogelaar's avatar
HansVogelaar
MVP
Dec 16, 2023

xd40c 

The second argument of XLOOKUP must be a single column (or row). You cannot specify a multi-column range.

Use (for example)

 

=INDEX(Table5,MATCH(TRUE,(Table5[TUFFALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CMW_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[TIPALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CENTERLINE_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)>0,0),0)

 

or

 

=FILTER(Table5,(Table5[TUFFALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CMW_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[TIPALOY_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)+(Table5[CENTERLINE_NUMBERS]=CROSS_REF_NUMBER_LOOKUP)>0)

 

Resources