Hi,

First, and unrelated, I simplified and made your formulas consistent:

=INDEX(F1:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)))
=INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)) )
=INDEX(F1:F5,MAX((MIN(A2:E5)=A2:E5)*ROW(A2:E5)))
=INDEX(A1:E1,MAX((A1:E5=MIN(A1:E5))*COLUMN(A1:E5)) )

Notice I expanded the Index range to F1.

Second, there will be a problem if there are two items with the same max or
min

Third the basic idea for doing top or bottom 5 or 10 would look like this:
=INDEX(F$1:F$5,MAX((LARGE(A$2:E$5,ROW(A1))=A$2:E$5 )*ROW(A$2:E$5)))
You can copy this down for as many rows as you need.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"SAM" wrote:

I have the following set of data:

shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons

I want to baisically form a summary page that shows the highest and lowest
values and then tells me what shop and what company the figure is from. I
have succesfully done this so far for the min and max values:

Company Shop
max value dixons shop3
min value dixons shop5

using the following formulas:

Max Value Company
=INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1))

Max Value Shop Number
=INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))

Min Value Company
=INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1)))

Min Value Shop Number
=INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)))

And these are working fine. Thing is i want the top five max values, and top
ten min values and their corresponding shop numbers....does anyone have any
idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and
B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really
really appreciate someones thoughts.....thank you so much anyone - i know
this is a complicated query...


Sam