Xlookup issue

[xd40c]

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

CMW_NUMBERS

TIPALOY_NUMBERS

CENTERLINE_NUMBERS

If I only search one column, as in below (CROSS_REF_NUMBER = WC-2510), I get the correct results.

=XLOOKUP(CROSS_REF_NUMBER_LOOKUP,Table5[CENTERLINE_NUMBERS],Table5[[CADI_NUMBERS]:[CENTERLINE_NUMBERS]],0)

Results:

CC2510

015-0123

C-2510 TIP

3327

F2-250

WC-2510

If I try to search columns 3-6 with the below formula, I am getting a value error.

=XLOOKUP(CROSS_REF_NUMBER_LOOKUP,Table5[[TUFFALOY_NUMBERS]:[CENTERLINE_NUMBERS]],Table5[[CADI_NUMBERS]:[CENTERLINE_NUMBERS]],0)

Result:

#VALUE!

Any ideas why this won't return the CROSS_REF_NUMBER results???

Thanks,

Chris Breidenbaugh

[#2 · Accepted solution]

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