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.Lookup_array is a range where to search for the largest values.Return_array is a range from which to extract associated data (matches).INDEX( return_array, MATCH(LARGE( lookup_array, k), lookup_array, 0)) To find the lowest N values in a list, the generic formula is: You can use any column letter and any row number for the expanding range reference, say A$1:A1 or C$1:C1.Įxcel formula to get bottom 3, 5, 10, etc. Please note that we used A$2:A2 just for convenience because our data begins in row 2. The ROWS function counts the number of rows in A$2:A3 and returns 2 for k, so the LARGE function outputs the 2 nd largest value. For example, in D3, the reference changes to A$2:A3. When copied to the below cells, the range reference expands by 1 row causing the k argument to increment by 1. In the topmost cell where you enter the formula (D2 in this example), ROWS(A$2:A2) generates 1 for k, telling LARGE to return the max value. To create such a reference, we lock the row coordinate in the first cell with an absolute reference (A$2) and use a relative reference for the last cell (A2). The ROWS function with an expanding range reference like ROWS(A$2:A2) generates the k values automatically. The LARGE function compares all the numeric values in a range supplied for the 1 st argument ( array) and returns the largest value based on the position specified in the 2 nd argument ( k). To find top 10 values in a column, copy the formula to 10 cells. To get top 5 values, copy the same formula to 5 cells. To pull top 3 values, copy the formula to 3 cells. For example, to find top N values in B2:B12, you enter the below formula in the topmost cell where you want the results to appear (D2), and then drag it through as many cells as needed.
0 kommentar(er)
