How do I find the largest numbers in a area, and then find the value of a number in the same row?-CodePudding

I cannot find a way to do this, is it possible (with excel functions or VBS)?

For example, these would be the initial values:

CodePudding user response：

If data is constantly updating, better to have an Excel Table (I named TB_NumVal), so the range index get automatically updated.

In cell: J2:

=SORT(FILTER(SORT(TB_NumVal,2), (ROW(TB_NumVal[Number])-1) 
  > ROWS(TB_NumVal[Number])-3))

Here is the output:

Explanation

We sort the data, then since we start on row 2 (row 1 is the header) we substract 1. So

ROW(TB_NumVal[Number])-1

will provide the row number starting from one.

ROWS(TB_NumVal[Number])

is the total number of rows, in our case 10.

Using a filter condition like this:

(ROW(TB_NumVal[Number])-1) > ROWS(TB_NumVal[Number])-3)

ensures only the last three row will be selected, then finally sorted the filtered result to the result in the image of the question.