XLOOKUP Formula working in certain sequence but not in others

[thewiseguy]

Hi all,

When the formula in column C was written like this:

=IFERROR(XLOOKUP(A2,Table_Fixture[MaskedPartNumber],Table_Fixture[ApplicationType],XLOOKUP(A2,Table_Lamp[MaskedPartNumber],Table_Lamp[ApplicationType],XLOOKUP(A2,Table_Materials[MaskedPartNumber],Table_Materials[ApplicationType],A2,Table_Accessory[MaskedPartNumber],Table_Accessory[ApplicationType],XLOOKUP(A2,Table_Control[MaskedPartNumber],Table_Control[ApplicationType],XLOOKUP(A2,Table_Rental[MaskedPartNumber],Table_Rental[ApplicationType],"",0)))))),"")

it did not work.

written as it is here; it does work.

v2023.4 - Copy.xlsm
	A	B	C	D
1	Mfg/Item	Item	Item Type	Manufacturer
2	MAXLITE 11W LED PL Lamp [GX24q-MCCT]	11W LED PL Lamp [GX24q-MCCT]	CFL Lamp	MAXLITE
3	MAXLITE 2X4 LED Flat Panel [WS-MCCT]	2X4 LED Flat Panel [WS-MCCT]
4	MAXLITE 4ft LED Wrap [23W-MCCT]	4ft LED Wrap [23W-MCCT]
5	MAXLITE 8ft LED Linear Highbay [65W-MCCT]	8ft LED Linear Highbay [65W-MCCT]
6	PHILIPS 5.5W LED Par20 Lamp [3K-90CRI]	5.5W LED Par20 Lamp [3K-90CRI]	PAR Lamp	PHILIPS
7	TBD Stranded Copper THHN Cable 12-AWG BLK/WHT/GRN	Stranded Copper THHN Cable 12-AWG BLK/WHT/GRN	Materials	TBD
8	ROSELLE Roll Off Dumpsters – 15 Yard	Roll Off Dumpsters – 15 Yard	Rental	Roselle
9	UNITEDRENTALS Boom, Articulated, 30-39' Diesel Day	Boom, Articulated, 30-39' Diesel Day	Rental	United Rentals
Sheet1

Cell Formulas
Range	Formula
A2:A9	A2	=VSTACK('Product (BOM)'!A2#,'Material (BOM)'!A2#,'Rental (BOM)'!A2#)
B2:B9	B2	=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C9	C2	=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber],Table_Rental[ApplicationType],XLOOKUP(A2,Table_Lamp[MaskedPartNumber],Table_Lamp[ApplicationType],XLOOKUP(A2,Table_Materials[MaskedPartNumber],Table_Materials[ApplicationType],(A2,Table_Accessory[MaskedPartNumber],Table_Accessory[ApplicationType],XLOOKUP(A2,Table_Control[MaskedPartNumber],Table_Control[ApplicationType],XLOOKUP(A2,Table_Fixture[MaskedPartNumber],Table_Fixture[ApplicationType],"",0)))))),"")
D2:D9	D2	=IFERROR(XLOOKUP(A2,Table_Rental[MaskedPartNumber],Table_Rental[Brand],XLOOKUP(A2,Table_Lamp[MaskedPartNumber],Table_Lamp[Brand],XLOOKUP(A2,Table_Materials[MaskedPartNumber],Table_Materials[Brand],(A2,Table_Accessory[MaskedPartNumber],Table_Accessory[Brand],XLOOKUP(A2,Table_Control[MaskedPartNumber],Table_Control[Brand],XLOOKUP(A2,Table_Fixture[MaskedPartNumber],Table_Fixture[Brand],"",0)))))),"")
Dynamic array formulas.

It seems like which ever string is last in the command, does not work properly. C3, C4 and C5 should populate the words, "Fixture."

Any ideas? I am stumped!

 

[Anonymous1378]

You're missing the XLOOKUP function for the table accessories table?

 

[thewiseguy]

no, there just happen not to be any accessories in this project.

the word "flat panel" is not populating

 

[thewiseguy]

The formula is the same here but i switched the first and last commands. It works - cell C3

v2023.4 - Copy.xlsm
	A	B	C	D
1	Mfg/Item	Item	Item Type	Manufacturer
2	MAXLITE 11W LED PL Lamp [GX24q-MCCT]	11W LED PL Lamp [GX24q-MCCT]	CFL Lamp	MAXLITE
3	MAXLITE 2X4 LED Flat Panel [WS-MCCT]	2X4 LED Flat Panel [WS-MCCT]	Flat Panel	MAXLITE
Product (BOM)

Cell Formulas
Range	Formula
A2:A6	A2	=SORT(UNIQUE(FILTER('Line Item (input)'!W3:W2002,('Line Item (input)'!W3:W2002 <>"")*('Line Item (input)'!W3:W2002<>0),FALSE)))
B2:B3	B2	=IFERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2)),"")
C2:C3	C2	=XLOOKUP(A2,Table_Fixture[MaskedPartNumber],Table_Fixture[ApplicationType],XLOOKUP(A2,Table_Lamp[MaskedPartNumber],Table_Lamp[ApplicationType],XLOOKUP(A2,Table_Materials[MaskedPartNumber],Table_Materials[ApplicationType],(A2,Table_Accessory[MaskedPartNumber],Table_Accessory[ApplicationType],XLOOKUP(A2,Table_Control[MaskedPartNumber],Table_Control[ApplicationType],XLOOKUP(A2,Table_Rental[MaskedPartNumber],Table_Rental[ApplicationType],"",0))))))
D2:D3	D2	=XLOOKUP(A2,Table_Fixture[MaskedPartNumber],Table_Fixture[Brand],XLOOKUP(A2,Table_Lamp[MaskedPartNumber],Table_Lamp[Brand],XLOOKUP(A2,Table_Materials[MaskedPartNumber],Table_Materials[Brand],(A2,Table_Accessory[MaskedPartNumber],Table_Accessory[Brand],XLOOKUP(A2,Table_Control[MaskedPartNumber],Table_Control[Brand],XLOOKUP(A2,Table_Rental[MaskedPartNumber],Table_Rental[Brand],"",0))))))
Dynamic array formulas.

 

[Anonymous1378]

If you don't have an XLOOKUP in place for table accessories, how do you expect the formula to check if there are no matches and proceed with the xlookup for the tables after the table accessories table?

 

[thewiseguy]

i do have one in there….table_accessory

the issue only affects whichever command is last. the last command is not working.

 

[Anonymous1378]

Just humour me and tell me what happens when you add an XLOOKUP to where the blank yellow box is?

 

[thewiseguy]

AHHHHH!

now i se what you were saying. I've been looking at this for an hour and totally missed it. Thank you! HUge help!