# Index Match with multiple tables

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!

# Re: Index Match with multiple tables

``=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.