Hello, I am struggling with the concept of using a formula that can capture the unit cost that depends on the ID category to get to the relevant table to use INDEX MATCH on. With unknown ID category (potentially hundreds) I would love to have a universal formula that does all the matching at the same time.
I attached an example below, I have a formula in F12 that would generate a unit cost but I've limited my array to the table I expected to use, and used an integer (2) that I suppose will need to be changed into its own INDEX formula if the total array includes all tables.
Many thanks in advance for any help!
=INDEX(INDIRECT(H3),2,MATCH(H5,INDIRECT(H4),1))You can try this INDEX / INDIRECT / MATCH formula with named ranges and an INDEX / MATCH which is in cell H4 in the example.
=INDEX($K$2:$K$27,MATCH($H$3,$J$2:$J$27,0))The named ranges are ID_1 for B2:D2 and QTY_ID1 for B1:D1 and so on in the example. You can add hundreds of named ranges in the worksheet. The INDEX / MATCH returns the QTY_ID from a reference table.
You can then enter the table in H3 and the quantity in cell H5 and the result is returned in H6 by the INDEX / INDIRECT / MATCH formula.