Excel Lookup and Data Validation

I made a break in my series of Excel tutorials, but now I am back with a new demo in the kalmstrom.com Tips section. This time I am demonstrating how to use the Excel lookup and data validation functions.

Shipping costs
When you buy goods that should be sent to you by truck, boat or railway, you of course want to pay as little as possible for the freight. At the same time you probably want to received the cargo as soon as possible, but this factor differs from case to case.

Look up costs
To judge what shipping option is the best in each case you need to see what the shipping costs would be for a specific cargo sent in different ways. In my demo I show how to do that.

I make Excel look up the shipping cost per item for each option I select and multiply it with the number of items in the cargo. The calculated sum will be shown on the sheet where I select shipping option, so it is very quick to compare the costs of the different alternatives.

Validate data
If I just write in the different shipping options in a column, someone will sooner or later write in something that is not present in the list of cost per shipping option. And then the calculation will crash!

It is safer and quicker to make Excel validate the data and let users select them instead of writing them in the cell. I achive this by using the data validation feature, which creates the dropdowns and gives an error message if someone still tries do write in a non-existing value instead of selecting from the existing ones.



Excel formulas
In this demo I use various Excel formulas, and if you are not familiar with them I advise you to have a look at my earlier tutorials on how to create Excel formulas for plus and minus, how to make Excel mulitpy and divide and how do create formulas with references between different Excel sheets.

Together they form a mini course in Excel calculations, but you can find several more Excel demos in the kalmstrom.com Tips section.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions