SOLVED

Xlookup issue

Copper Contributor

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_NUMBERSCADI_ITEM_NUMBERSTUFFALOY_NUMBERSCMW_NUMBERSTIPALOY_NUMBERSCENTERLINE_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:

CC2510015-0123C-2510 TIP3327F2-250WC-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

1 Reply
best response confirmed by xd40c (Copper Contributor)
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)

 

1 best response

Accepted Solutions
best response confirmed by xd40c (Copper Contributor)
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)

 

View solution in original post