A couple years ago I developed an Excel custom function that provides a simply way to get information from a table (Excel range).
This Excel spreadsheet provides an example. On the “Example” tab you’ll see how to use the function. Enter the “qtable” custom function into a cell along with a key and a code; if the key is in the table it will return a value.
The keys are stored on the “Cost Table” tab. An Excel defined name – “CostTable” – is used to define a range that the “qtable” function uses to lookup values. If records are added to the table you need to redefine the range of “CostTable.”
When the Excel workbook first opens, it loads the “CostTable” range into memory -- an array. If you enter new values into the table, click the “Load Costs” button and the new values are loaded into memory.
I have watched as analysts used the qtable function to simplify complex models. So feel free to use this model and modify the spreadsheet to fit your needs.