How to find the nearest value in Excel table Example

Every Excel user can easily find the minimum or maximum value in a range of numbers using functions like =MIN(), =MAX(), =SMALL(), or =LARGE(). You can also easily find the position of a specific value in a range of cells using the =MATCH() function. However, in this example, we'll explore a more interesting solution that allows you to find the nearest value in Excel. We'll create a formula that can locate the closest match to the user's request. For instance, if your data range doesn't contain values that exactly match the user's query, the MATCH function returns a #N/A error. But the user may be satisfied with an approximate result, whether it's slightly less or slightly greater than the query. The significant advantage of this formula is that it eliminates the need for conditional sorting to solve such problems.



Excel formula to find the nearest value

Search Nearest Value in Excel

Let's take a specific situation as an example. A company is moving its inventory to a new location, and to fill the truck with products of the same package volume (e.g., letter-sized A4 office paper with 500 sheets), they need to add 220 more packs. However, they want to avoid mixing different product types during the move. In other words, they want to minimize the sorting of products during the relocation. Here are the remaining quantities of different product types:

Remaining Inventory

We need to find the nearest smaller value in Excel. To locate the product type with the closest available quantity (not exceeding 220 packs), we can create the following formula:

  1. In cell E2, enter the value 220, which represents the number of packs of office paper needed to fill the remaining space in the truck.
  2. In cell E3, enter the following array formula:
    3. =INDEX(B3:B12,MATCH(MIN(ABS(B3:B12-E2)),ABS(B3:B12-E2),0))
  3. To confirm the entry of the formula, press Ctrl+Shift+Enter, as the formula needs to be executed as an array formula. If you've done it correctly, you'll notice curly braces in the formula bar.

Here's the result of the formula's calculation to find the nearest value:

Finding the Nearest Value

As a result, you'll load a set of product type-9 (195 packs) as it closely matches the required quantity of 220 packs. The truck will be filled as efficiently as possible, minimizing the sorting of products at the new location.

Search Nearest Value in Excel

download file Download

You can use a similar formula for finding the nearest value in an Excel range, not limited to a single column.

Principle of Finding the Nearest Value Using the Formula:

  1. Subtract the initial value in cell E2 from each of the remaining quantities in the range B3:B12. This creates a conditional table of values equal to this difference, with the number of cells matching the number of values in the range B3:B12.
  2. The ABS function returns the absolute value of each number in the conditional table, effectively converting all negative values to positive ones.
  3. Find the smallest value using the =MIN() function among the absolute values. The =MATCH() function then returns the position in the conditional table for the smallest value found by the MIN function.
  4. The result obtained from the MATCH function is an argument for the =INDEX() function, which returns the value in a cell corresponding to the position in the B3:B12 range.

Other Excel Possibilities for Value Search

Searching for cells containing specific values in a table with thousands of rows can be quite time-consuming. However, applying appropriate Excel formulas can reduce this task to a couple of minutes or less. We'll examine practical formulas for finding data in Excel tables, along with examples of how to use them. The next articles will cover dozens of search formulas. The operation of each formula will be explained in detail and illustrated with diagrams. These formulas can help you determine where the values you need are in the table, as well as provide the returned result based on user query criteria.

In addition, auxiliary Excel tools related to information search will be presented. For example, we'll look at conditional formatting, which allows you to highlight the data you're interested in. We'll also explore the capabilities of auto-filtering, which quickly displays only the data you need on the screen. You'll learn how to open the necessary data ranges on the screen in a few mouse clicks without repeating the same complex filtering actions.

We'll also discuss how to sort data efficiently to facilitate visual analysis. Proper sorting can enhance data readability and comprehension, as well as help you structure data for rapid visual value search, enabling more effective decisions during visual analysis. Excel offers many features in this area. You can sort data based on numerous criteria and columns, as well as based on cell formatting. The upcoming articles will introduce a range of effective sorting methods that are less known to most Excel users.