How to find the Nth value in Excel

To sort in ascending or descending order on Excel, we can use the existing Sort tool. However, this method only applies to documents with short and simple content. In case the user has to work with data tables that need complex comparison, find the largest, smallest value, or use the results to calculate into other formulas, it is necessary to do other things.

The following article will guide you how to find the largest, smallest, second largest value, . in the data table in Excel. This approach will apply primarily to the highest, smallest payroll tables, transcripts, and value tables.

1. How to rank on Excel with RANK function
2. How to arrange incremental dates in Excel
3. How to arrange names in alphabetical order in Excel

1. Guide to finding the Nth highest salary

We will work with Excel tables with data and requirements as shown below.

How to find the Nth value in Excel Picture 1

Step 1:

To find the person with the second highest salary in the above table, first click on the box showing the result is cell D9 in the table and then click on Formulas .

How to find the Nth value in Excel Picture 2

Then click on Insert Function , or press fx as shown below.

How to find the Nth value in Excel Picture 3

Step 2:

Appear the Insert Function interface for users to set items.

1. Or chọn một phần: Hãy chọn Statistical.
2. Select a function: Select LARGE.

Finally click OK to agree.

How to find the Nth value in Excel Picture 4

Step 3:

The Function Arguments dialog box appears with the settings shown below.

1. Array enter the data area you want to rank. In the above example, D2: D7.
2. K to enter what you want to find is number 2 with the second highest wage earner.

The preview will also show preview results for you to know. Click OK to enter the results into the data table.

How to find the Nth value in Excel Picture 5

The result will look like the image below.

How to find the Nth value in Excel Picture 6

Note to readers:

1. K will be the rank you want to find in the list, the default will be 1, the 2nd largest will be 2, .
2. If K is greater than the number of people in the list, the #NUM error will be reported. For example, there are 7 people in the list but entering K> 8 will report an error.
3. The formula for finding this kth largest value is = LARGE (Array, k) , with Array being the data area that wants to find the value and k is the rank.

2. The highest way of calculating the 3-person salary

We will use the LARGE function 3 times in the formula and add it up. The formula will be = LARGE (D2: D7,1) + LARGE (D2: D7,2) + LARGE (D2: D7,3) and press Enter.

How to find the Nth value in Excel Picture 7

The result will be the highest 3-person salary as shown.

How to find the Nth value in Excel Picture 8

3. How to find low wages click in the table

Step 1:

In the input box for the low salary, click also on Formulas, select Insert Function and then perform the following settings.

1. Or chọn một phần: Hãy chọn Statistical.
2. Select a function: Select SMALL.

Click Ok to proceed.

How to find the Nth value in Excel Picture 9

Step 2:

Also appeared Function Arguments box, enter the settings according to the following items.

1. Array selects the data area D2: D7.
2. K enter the rank you want to search for as the lowest with number 1.

You will immediately see the results with the lowest salary in the table. Click OK to proceed.

How to find the Nth value in Excel Picture 10

The lowest amount results are shown in the table below. The input formula for this function is = SMALL (Array, k) , with Array being the data area and k being the rank.

How to find the Nth value in Excel Picture 11

So you can easily search for people with rank k in the data table. In fact, we will use two functions: LARGE to find high value rankings and SMALL function to find low ranking data. The order of the rank will be shared as 1, 2, 3, . onwards.

See more:

1. How to arrange alphabetical order in Google Sheets
2. How to use the COUNT function in Excel
3. How to combine Sumif and Vlookup functions in Excel

I wish you all success!