Looking up values in 2-Dimensional tables


Office Improvement

Microsoft Excel Tip: I was recently working on process for a manufacturing company, where they had costing in tables. Typically, the materials in rows and across columns were size, ratings, type, etc. depending on the item.

To retrieve a price, this required a look-up on material and then a cross lookup on the type / rating /size. Relatively simple visually for a human. Here is a quick tip to take this to Excel:

The formula used to get the Price (F3):
=VLOOKUP(C2,B6:F12, MATCH(C3,B6:F6, 0), FALSE)

The Material (C2) and Specification (C3) are dropdown lists done with Data Validation (Data tab in menu ribbon). You might want fixed table selection with “$” if you are going to use it in many places or copy. E.g. $B$6:$F$12

Some ideas on where else this might be useful where there are 2-Dimensional tables:

ScenarioRowsColumns
Sales / ProductionProductsMonths
InvestmentsTypes%…

View original post 14 more words