In Excel 2019, Excel 2016 and earlier versions, this formula won't work. However, please keep in mind that XLOOKUP is only available in Excel 365. In this case, LARGE returns the k-th largest number directly to XLOOKUP as the lookup value.Ĭompared to the INDEX MATCH formula, this syntax is much simpler. Microsoft 365 subscribers can achieve the same results by using the new XLOOKUP function: This number goes to the row_num argument of INDEX, simplifying the formula further:įinally, the INDEX function returns the value from the 9 th row in the range A2:A12, which is "Nick". The MATCH function determines the relative position of 5.57 in B2:B12, which is 9. So, after replacing the LARGE function with its output, the formula reduces to: The LARGE function gets the k-th largest value and passes it to the lookup_value argument of MATCH.įor instance, in F3, we are looking for the 1 st largest value, which is 5.57. To get bottom 3 results, the formula in E3 is: For this, just use the SMALL function instead of LARGE. In a similar manner, you can get matches of bottom N values. Where A2:A12 are the names (return_array), B2:B12 are the results (lookup_array) and D3 is the position from largest ( k). To retrieve the names, the formula in F3 is: To extract top 3 results, the formula in E3 is:īecause the ranks are typed in separate cells, the ROWS function is not needed in this case - we simply reference the cell containing the k value (D3). In the table below, you can find top 3 values by using the following approach. K is position of the highest value to look for.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |