Forum Discussion
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 ...
=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.