2/12/2023 0 Comments Excel help if then formulaWe want to find the closest value, irrespective of whether the value is less than or greater than the value in E2. 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: 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |