Index Match with multiple tables

Occasional Visitor

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!


1 Reply



You can try this INDEX / INDIRECT / MATCH formula with named ranges and an INDEX / MATCH which is in cell H4 in the example.


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.

index indirect match.JPG

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.