Large spreadsheets can quickly become unwieldy, especially when you try to find specific information buried within volumes of data. Since the Lookup Wizard was retired in Microsoft Excel 365, the easiest and most effective method of searching a spreadsheet is to use the powerful VLOOKUP and HLOOKUP functions. This article provides an overview of these functions and examples showing how to use them.
First is the VLOOKUP (Vertical Lookup) function. The sample worksheet uses a discount matrix for items purchased in bulk. We are trying to determine how much of a discount to give for a particular purchase order, based on the weight of the items purchased. The formula is:
Do you have a shared worksheet in Microsoft Excel 365 containing important data or carefully-constructed formulas you don’t want altered, accidentally or deliberately? Perhaps it’s the standard hourly rate. Maybe it’s equipment part numbers, or a shipping-cost calculation. Whatever it is, people should just keep their hands off it!
Fortunately, Excel makes it possible to prevent people from changing things. The following steps show how to protect critical data while allowing users to enter information in only the fields you want them to access.
Keep in mind that protecting worksheet data is different than protecting a workbook with a password. The latter security measure is to ensure no one can access a workbook without the proper credentials — a key to get in. Protecting the cells in a shared worksheet means that the cells or range cannot be modified.