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:
|Sales / Production||Products||Months|
View original post 14 more words