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 mulitpy 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 reading the kalmstrom.com Blog post about Excel Lookup References and Data Validation and in studying the Tips article about Excel formulas between sheets.