Substitute for a LARGE xlookup formula?

[thewiseguy]

Hi all. Someone mentioned to me that the formulas I am using are ridiculous (I don't disagree). Is there something other than VBA that would simplify this formula? Basically I am constantly looking up data over multiple tables (and the number of tables does increase form time to time). Any help or direction is greatly appreciated!

=IFERROR((XLOOKUP(B4,Tbl_Area[MaskedPartNumber],Tbl_Area[BooksPartNumber],XLOOKUP(B4,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[BooksPartNumber],XLOOKUP(B4,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[BooksPartNumber],XLOOKUP(B4,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[BooksPartNumber],XLOOKUP(B4,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[BooksPartNumber],XLOOKUP(B4,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[BooksPartNumber],XLOOKUP(B4,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[BooksPartNumber],XLOOKUP(B4,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[BooksPartNumber],XLOOKUP(B4,Tbl_Linear[MaskedPartNumber],Tbl_Linear[BooksPartNumber],XLOOKUP(B4,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[BooksPartNumber],XLOOKUP(B4,Tbl_Strip[MaskedPartNumber],Tbl_Strip[BooksPartNumber],XLOOKUP(B4,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[BooksPartNumber],XLOOKUP(B4,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[BooksPartNumber],XLOOKUP(B4,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[BooksPartNumber],XLOOKUP(B4,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[BooksPartNumber],XLOOKUP(B4,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[BooksPartNumber],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[BooksPartNumber],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[BooksPartNumber],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[BooksPartNumber],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[BooksPartNumber],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[BooksPartNumber],XLOOKUP(B4,Tbl_Recessed_Retrofit_Kit[MaskedPartNumber],Tbl_Recessed_Retrofit_Kit[BooksPartNumber],XLOOKUP(B4,Tbl_Linear_Retrofit_Kit[MaskedPartNumber],Tbl_Linear_Retrofit_Kit[BooksPartNumber],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[BooksPartNumber])))))))))))))))))))))))))))))))))),"")

 

[Cubist]

You can use VSTACK to aggregate all the tables.

 

[thewiseguy]

You can use VSTACK to aggregate all the tables.

any examples of this? Not sure how to handle.

 

[Cubist]

Something like this.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Category		Name	Category		VSTACK			Lookup Value	Result
2	Apple	Fruit		Orange	Fruit		Apple	Fruit		Banana	Fruit
3	Banana	Fruit		Pineapple	Fruit		Banana	Fruit
4	Lettuce	Vegetables		Carrot	Vegetables		Lettuce	Vegetables
5							Orange	Fruit
6							Pineapple	Fruit
7							Carrot	Vegetables
Sheet2

Cell Formulas
Range	Formula
G2:H7	G2	=VSTACK(Table1,Table2)
K2	K2	=XLOOKUP(J2,G2:G7,H2:H7,"")
Dynamic array formulas.

 

[thewiseguy]

Something like this.

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Category		Name	Category		VSTACK			Lookup Value	Result
2	Apple	Fruit		Orange	Fruit		Apple	Fruit		Banana	Fruit
3	Banana	Fruit		Pineapple	Fruit		Banana	Fruit
4	Lettuce	Vegetables		Carrot	Vegetables		Lettuce	Vegetables
5							Orange	Fruit
6							Pineapple	Fruit
7							Carrot	Vegetables
Sheet2

Cell Formulas
Range	Formula
G2:H7	G2	=VSTACK(Table1,Table2)
K2	K2	=XLOOKUP(J2,G2:G7,H2:H7,"")
Dynamic array formulas.

so am I essentially stacking all of my tables into 1?

 

[Cubist]

Correct.