Forum Discussion
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 | 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
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)
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)