Home >Tips >Excel lookup function and data validation kalmstrom.com site map icon Site map  

Excel Lookup Function and Data Validation

An Excel tutorial by Peter Kalmstrom

Excel iconThe possibility to tell Excel to calculate is a crucial feature and the most important reason to use it. In other demos Peter Kalmstrom has shown how to create formulas for plus and minus and how to make Excel multiply and divide.

In the demo below Peter shows how to use the Excel lookup function and how to validate data so that users cannot enter the wrong data in the sheet.

Excel sheet for lookup

As an example Peter uses a calculation of prices for cargo that is shipped in different ways. He has a list with the cargo, the number of items to be shipped and the shipping method, and he wants to see the costs for the various shipping options in each case.
Excel list for lookup

On a second sheet in the same workbook Peter has another list with the costs for the different shipping options, and in the demo he shows how to make Excel look up the cost for the selected option in that list.

Excel list for lookupPeter also shows how to validate the data, so that users cannot write in an option that is not present in the list of shipping costs. Instead they can select the options from a dropdown, and should they still write in a non-existent value they will get an error message explaining this.

Peter uses Excel 2013 for his demo, but the Excel lookup function and data validation are the same for earlier versions of Excel. 

You might also be interested in the Tips article about Excel formulas between sheets.

Products Buy FAQ Services Tips Services Books Contact About Us Tools

Security and integrity

Copyright  Kalmstrom Enterprises AB  All rights reserved