Get Shift Done: Tips and Tricks
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:
The first argument, colored red here for visibility reasons, references the value we wish to locate. Here, that’s the contents of cell B2, which contains the weight of the purchase order. (Alternatively, a hard value can be used if you want to reference a static string and not a cell, such as 231.)
The second argument, shown in blue, defines the range of cells to include in the search. This example has us searching C2 through E10, representing the discount table.
The third argument, colored green, is the column within this range that contains the value to query. By entering 3, we tell Excel to pull a value from the third column in the defined range (E2-E10). This argument ensures that our result is the percentage we are seeking.
The fourth and final argument, shown in orange, is optional. If you leave this blank, Excel automatically sets the value to TRUE as the default. The TRUE argument instructs Excel to return the closest approximation to the queried value (B2).
Using our example, the value we are seeking is not specifically defined; there is no 231 on the spreadsheet. Therefore, the formula returns the closest result in the defined range. If you change this argument to FALSE, the formula searches for an exact match. If none is found, an #ERROR is returned.
In summary, this formula searches our minimum and maximum weight specifications (C2-E10) to find the closest approximate match to our purchase weight (B2) and returns the matching discount percentage (column 3).
Now that you understand how to use VLOOKUP, the HLOOKUP (Horizontal Lookup) formula should be simple. This formula uses the same syntax as VLOOKUP, and works in much the same way. Instead of returning vertically formatted data for a specific result in a column, HLOOKUP searches a horizontally formatted table or array and returns a result from a row.
The example below has the same data and objective, but is reformatted horizontally. Following the formula, we search for the value (B5) within the specified range (B1-I3), and request an approximate result (TRUE) from the Discount Row (3).
Congratulations! You now understand the basics of the LOOKUP function in Excel 365! Whether you are trying to add simple automation to a form or searching a massive spreadsheet for data points, this powerful but simple formula can help advance your project and save valuable time!
GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.