Excel look up value

The formula below will give you the address of the cell where the 2nd largest value was found in the list A1:A10. Of course, you know how to modify this to return the position of the 30th-largest.

=ADDRESS(MATCH(LARGE(A1:A10,2),A1:A10,0),1,4,1)

This formula uses LARGE(A1:A10,2) as the look-up value for a MATCH function which returns the row number which is used to specify the cell in column 1 (which is column A). The remaining two code numbers, 4 and 1 specify the format of the address. 4 suppresses the $ signs, making the address relative and the final 1 specifies the output format as A1-type.

Note that it's important to start the look-up range in row 1. If your range can't be specified in this way you need to compensate for a different start because MATCH will return its result relative to the look-up range, meaning if the match was found in the 3rd row of the range the sheet row will be different, depending upon which roiw the range starts on.

I'm not sure that this is what you requested, siince "I would like to know the exact value that is given by the formula" is ambiguous. The LARGE function does give you the exact value. But with the help of the above formula you will know where the value was found.

Lex

You imply that your numbers are in several rows and columns (rather than in a single coulmn, as addressed neatly in the answer from Variatus). In that case, you can use the LARGE function in conjunction with an array formula to locate the nth value.

In the attached sheet there's a 3x10 array (shaded in light gold) which I've defined as ValueArray using the Name Manager. In the box F3:G7, you just have to enter n in the yellow cell G3 (where n is between 1 and the maximum number of cells in the arrray, 30 in this case).

G4 is a regular formula which returns the nth highest value, taking n from G3:

=LARGE(ValueArray,G3)

G4 is an array value to return the Row where that nth value in G4 is first found in the array:

{=MIN(IF(ValueArray=G$4,ROW(ValueArray)))}

and has to be confirmed with Ctrl+Shift+Enter.

G5 is a similar array formula but locates the first column. G7 then uses a regular formula to return the A1 format address of the cell (from the row in G4, column in G5):

=ADDRESS(G5,G6,4,1)

 You can call the array what you like and change the size but be careful when entering the array formulae. You could combine G7,G6 and G5 into a single array formula if you like!

Hope this is what you're looking for.