Indexing Help

[Mechixx]

Hello there everyone!

So ive been having trouble with figuring out how to go about this.

What i have is a table on a sheet called 'Tooling Data'. The table is called 'ToolingData_Table' and it has a few different columns, one column of which is full of part numbers, another with # of corners, # of pieces per corner and lastly a Qty/Week column.

On another sheet called 'Production Data' i have a table called 'ProductionData_Table' which has a Part # column, and a production/week column

So what i want to do is on my 'Tooling Data' sheet in the 'ToolingData_Table' i want a formula in the 'QTY/Week' column that looks up the value for part number in its row, use that value to then correspond it with the 'ProductionData_Table' to get the corresponding production number, and then divide that number by the product of (# of corners * # of pieces per corner)

Heres a table to explain what i mean:

This is a barebones of my 'ToolingData_Table' on sheet 'Tooling Data'

Dept.	Part #	Op #	Type of Item	Item Code	Stock #	# of Corners	# of pieces per corner	QTY/Week
auto	xyz	10	Insert	qwerty	1234	4	250	=9000/(4*250)

<tbody>
</tbody>

Here is a barebones of my 'ProductionData_Table' on Sheet 'Production Data'

Part #	Production/Week
xyz	9000
abc	10000

<tbody>
</tbody>

So looking at these examples, i want a formula that i put into the QTY/Week column that grabs the part # from its row [xyz], then uses that part number in the 'Production Data_Table' to get the 'Production/Week' value [9000], then have that divided by the product of (# of corners [4] * # of pieces per corner [250])

i was thinking of using the Index function since i want the formula to be flexible and able to work if i add/delete columns, etc. and each of these tables will grow over time as more parts and inserts are used

any help on this would be greatly appreciated since I'm not too strong with the INDEX function yet. Thanks a ton in advance!

 

[Eric W]

You could do something like:

=INDEX(ProductionData_Table[Production/Week],MATCH([Part '#],ProductionData_Table[Part '#],0))/(['# of Corners]*['# of pieces per corner])

Note the ' symbol before each # symbol - that's a quirk of Excel's table header rules.

 

[Max1616]

Try this:
=INDEX(ProductionData_Table[[#All],[Production/Week]],MATCH([Part '#],ProductionData_Table[[#All],[Part '#]],0))/(['# of pieces per corner]*['# of Corners])

 

[Mechixx]

Try this:
=INDEX(ProductionData_Table[[#All],[Production/Week]],MATCH([Part '#],ProductionData_Table[[#All],[Part '#]],0))/(['# of pieces per corner]*['# of Corners])

Thanks for the quick reply! i notice theres a bit of a difference between your response and the other response i had received. yours has [#All] in front of some things, whats the reason for the[#All]?

 

[Max1616]

It's just how it populated when I create the example spreadsheet. Both formulas will accomplish the same thing.

 

[Mechixx]

It's just how it populated when I create the example spreadsheet. Both formulas will accomplish the same thing.

Ahh ok, gotcha.

 

[Mechixx]

Thank you both for your fast reply! Exactly what i needed to accomplish and works great!

It was a good tip about the ' in front of the # signs as that would have given me trouble for sure.

Cheers!