So we can use this function in our original example: The formula used does not require nesting ABS and MIN functions, so it’s easy and quick. The second method is way simpler and uses the XLOOKUP Function. Using XLOOKUP to Find the Closest Value in Excel (for Excel 365 and Later Versions) Note: If there is a tie in the matching, then the formula will return the first match. In this case, the ‘ Stacking Chairs” are the 2nd product (or are at the second index of our list), so the formula returns “Stacking Chairs” at its result.
![find nearest printme lmu find nearest printme lmu](http://libapps.luc.edu/digitalexhibits/files/medium/a56c7a8da1099a6557d9775be176ea7308edc371.jpg)
This is because those values are l ess than the value in E2. Notice that some of the above values are negative. Together, the formula to find the product corresponding to the price closest to the value in E2 is:
#Find nearest printme lmu how to#
We can use this combination to find out the index of the price closest to the given value and then return the name of the product corresponding to that price.īut the question remains – how to find the closest value?įor this, we will need a combination of the ABS, MIN, and MATCH functions. However, when combined together the two formulas can look up a value in a cell from a table and return the corresponding value in another cell in the same row or column. The MATCH function provides the relative position or ‘index’ of an item in a range of cells, while the INDEX function provides the contents at a given index of a range of cells. Individually these functions don’t do much. The combination of the INDEX and MATCH functions in Excel is a powerful one. Using INDEX-MATCH Formula to Find the Closest Match in Excel To find out which product’s cost is closest to the value in cell E2, you can use one of the following two methods. The price could be less than or greater than the given amount, but among all the product prices, it has to be the closest.
![find nearest printme lmu find nearest printme lmu](https://careers.insidehighered.com/images/_display/job/2460963/facebook.png)
Now say you are looking for a product that is priced closest to a given amount (specified in cell E2 of the screenshot below). The above dataset consists of unit prices of different products.