Based on this post, it appears that your data might be in columns, so I'll
work with that.

For this example:
Your data is in cells A1:A10
The secondary data you want is in cells C1:C10

E1: 3 (this is the column reference to pull data from)

F1: =INDEX(A1:C10,MATCH(LARGE(A1:A10,2),A1:A10,0),E1)

In this case, the function returns the value of the cell in A1:C10 that is 6
rows down from the top and 3 cells from the left in that range.

One thing to be aware of:
If there was more than one 7 in your range....the LARGE function would call
one of them 2nd largest and the next would be 3rd largest.


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Frank Drost" wrote:

Thanks Ron. That command works.
However, I actually made a small, but crucial mistake in my query. I am not
actually after the second largest value, but its location in its row. For
instance, as in my example, the number 7 is the second largest value, and its
location in that row is nr 6. And then to make the complication complete, I
then want as answer the value of cell 6 in row X (x is a variable here, but
will often be the first row in my table). To do that I think I need to do
conditional formatting. It seems I have to use LARGE, but what else? Can you
help me with this?

Thanks.



"Ron Coderre" wrote:

Try this:
For your data in cells A1:J1

K1: =LARGE(A1:J1,2)

That returns the second largest value in the referenced range.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Frank Drost" wrote:

In a table, I need to find for each row the second largest value. Undoubtedly
this can be done with conditional formatting, but I don't know how. Something
like find maximum for a range of cells as long no cell is the maximum of that
row, right? For instance, the following row has:
0 2 4 5 3 7 8 3 2 6
I want to have as answer nr 7 (8 is maximum, 7 is next highest)

does anyone know how to do that?

Thanks