For Max shop # and value,
Enter this *array* formula in say H2 for shop #:

=INDEX(A$1:E$1,MAX(IF(A$2:E$5=LARGE(A$2:E$5,ROWS($ 1:1)),COLUMN(A:E))))

And this formula right next to it in I2:

=LARGE(A$2:E$5,ROWS($1:1))

*After* the CSE entry of H2, select both H2 and I2 and copy down that 2 cell
selection as needed.

For Min shop # and value,
Enter this *array* formula in say J2 for shop #:

=INDEX(A$1:E$1,MAX(IF(A$2:E$5=SMALL(A$2:E$5,ROWS($ 1:1)),COLUMN(A:E))))

And this formula right next to it in K2:

=SMALL(A$2:E$5,ROWS($1:1))

*After* the CSE entry of J2, select both J2 and K2 and copy down that 2 cell
selection as needed.

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"SAM" wrote in message
...
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