Need to Add 2 Additional statements to this formula

[thewiseguy]

=IFERROR(IFERROR(VLOOKUP(B12,ECM_BOM_Table,7,0),VLOOKUP(B12,CONTROLS_BOM_TABLE,7,0)),VLOOKUP(B12,ACCESSORIES_BOM_TABLE,7,0))

I need to add:

VLOOKUP(B12,MATERIALS_BOM_TABLE,7,0)
VLOOKUP(B12,RENTAL_EQUIPMENT_BOM_TABLE,7,0)

Any help would be greatly appreciated!!!

 

[GuzmanServices]

Let's try the following formula:

=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(B12,ECM_BOM_Table,7,0),VLOOKUP(B12,CONTROLS_BOM_TABLE,7,0)),VLOOKUP(B12,ACCESSORIES_BOM_TABLE,7,0)),VLOOKUP(B12,MATERIALS_BOM_TABLE,7,0)),VLOOKUP(B12,MATERIALS_BOM_TABLE,7,0))

 

[thewiseguy]

Let's try the following formula:

=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(B12,ECM_BOM_Table,7,0),VLOOKUP(B12,CONTROLS_BOM_TABLE,7,0)),VLOOKUP(B12,ACCESSORIES_BOM_TABLE,7,0)),VLOOKUP(B12,MATERIALS_BOM_TABLE,7,0)),VLOOKUP(B12,MATERIALS_BOM_TABLE,7,0))

thank you !

 

[Alex Blakenburg]

Since you have MS 365 there are lot of of options.
You could use the XLookup built in not Found option and nest your XLookups.
You would need to put in the correct column headings.

=XLOOKUP(B12,ECM_BOM_Table[Col1],ECM_BOM_Table[Col7],
XLOOKUP(B12,CONTROLS_BOM_TABLE[Col1],CONTROLS_BOM_TABLE[Col7],
XLOOKUP(B12,ACCESSORIES_BOM_TABLE[Col1],ACCESSORIES_BOM_TABLE[Col7],
XLOOKUP(B12,MATERIALS_BOM_TABLE[Col1],MATERIALS_BOM_TABLE[Col7],
XLOOKUP(B12,RENTAL_EQUIPMENT_BOM_TABLE[Col1],RENTAL_EQUIPMENT_BOM_TABLE[Col7],
"Not Found")))))

Another option is that since the lookup typically looks from top to bottom vstack would simplify the formula quite a bit, with something like the below:
You would want to compare its performance though.

=VLOOKUP(B12,VSTACK(ECM_BOM_Table,ECM_BOM_Table,CONTROLS_BOM_TABLE,ACCESSORIES_BOM_TABLE,MATERIALS_BOM_TABLE,RENTAL_EQUIPMENT_BOM_TABLE),7,FALSE)

If performance was too slow you could do VStack once on a hidden sheet and refer to that in the VLookup so that the VStack is performed only once not on each row.

 

[thewiseguy]

Since you have MS 365 there are lot of of options.
You could use the XLookup built in not Found option and nest your XLookups.
You would need to put in the correct column headings.

=XLOOKUP(B12,ECM_BOM_Table[Col1],ECM_BOM_Table[Col7],
XLOOKUP(B12,CONTROLS_BOM_TABLE[Col1],CONTROLS_BOM_TABLE[Col7],
XLOOKUP(B12,ACCESSORIES_BOM_TABLE[Col1],ACCESSORIES_BOM_TABLE[Col7],
XLOOKUP(B12,MATERIALS_BOM_TABLE[Col1],MATERIALS_BOM_TABLE[Col7],
XLOOKUP(B12,RENTAL_EQUIPMENT_BOM_TABLE[Col1],RENTAL_EQUIPMENT_BOM_TABLE[Col7],
"Not Found")))))

Another option is that since the lookup typically looks from top to bottom vstack would simplify the formula quite a bit, with something like the below:
You would want to compare its performance though.

=VLOOKUP(B12,VSTACK(ECM_BOM_Table,ECM_BOM_Table,CONTROLS_BOM_TABLE,ACCESSORIES_BOM_TABLE,MATERIALS_BOM_TABLE,RENTAL_EQUIPMENT_BOM_TABLE),7,FALSE)

If performance was too slow you could do VStack once on a hidden sheet and refer to that in the VLookup so that the VStack is performed only once not on each row.

Will the XLOOKUP method work if the tables are actually just named ranges? They are not tables.

 

[Alex Blakenburg]

Will the XLOOKUP method work if the tables are actually just named ranges? They are not tables.

Yes but tables have the advantage of automatically expanding when you add more rows.