# It’s Hard To Do Advanced Search Inside Excel 365. But Here’s How (Stay With Us)

#### 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!

https://upscri.be/6d0ed7

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.