Populate an array of table[column] names from a column of another table and return the max number

Question

I have a table with a list of manufacturers (Manufacturer 1, Manufacturer 2, Manufacturer 3). Then for each manufacturer listed in that table there is a corresponding table that list all the items that manufacturer offers. Those tables have multiple columns for different criteria but they all have a column named position. All those tables are named consistently with something at the beginning of the name, the manufacturer's name and the column name being position.

So the table and column name is something like this:

Metal_Supplier_Manufacturer_1[Position].

I want a formula that does what this formula does: =MAX(Metal_Supplier_Manufacturer_1[Position],Metal_Supplier_Manufacturer_2[Position],Metal_Supplier_Manufacturer_3[Position])

I want to return the highest number in the position column but I want it to look at the table of manufacturers to populate the names of the tables. Each manufacturer table has about 30-50 manufacturers listed and will keep growing so I don't want to keep coming back to this formula to update it when something gets added.

Edit: Sample I added a sample picture. I want to replace the formula in D1763 with one that does the same thing but looks at the Manufactures table to populate the array of table names, all just looking at the position column to return the max number. So then if I add a Manufacture 4 to the Manufactures table and a corresponding table consistently named like the others with a column named position (Metal_Supplier_Manufacturer_4[Position]), my formula would adjust and add the new table to the array.

I require a solution using only worksheet formulas.

Answer 1

Easiest way to do this is going to be to add a helper column to your manufacturer table (side note: I would really encourage you to spell 'manufacturer' correctly more consistently).

1. Add a 'MAX' field to the Manufacturers table. Use INDIRECT to build a structured reference to get the MAX of the correspondingly named table. I named my tables "Mfr_Table[n]" for this demo because I didn't feel like typing out the names of your tables, so you'll update the strings in this formula:

=MAX(INDIRECT("Mfr_"&[@MANUFACTURERS]&"[Position]"))

But you can see how you use INDIRECT to combine literal strings with cell references. Here's how that looks in action:

The advantage here is that because it's in a Table, it automatically updates when you add rows to it, so once you've added Mfr_Table4, you just go to the next line (D8 in this example), add "Table4" and the new MAX is pulled in.

2. The next part should now be obvious. Your overall MAX formula:

=MAX(Manufacturers[MAX])

Answer 2

I think it will work in excel 2010 but I can't test it:

there are various components (A2=$F$2:$F$5) tests if whatever in cell A1 matches with what is in the table in column F. Similar explanation for (B2=$G$2:$G$5). These parts of the formula normally return a TRUE or FALSE value, which is not directly helpful. However, adding the -- in front of it causes it to convert to 1 or 0. The last part is the value you are seeking (only works with numbers). By multiplying these three components together you are creating an array of values. The Max() grabs the largest value from the array.

Let me know if you nee help with my explanation.