Complicated vlookup/min-max query...please help

S

SAM

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
 
M

Mike H

Sam,

These formula look familiar, I think I did these a couple of days ago!!

Anyway, you want the largest 10 shops and companies:-

Largest 10 shops array enter (see below) and drag down 9 rows.
=INDEX($A$1:$E$1,MAX(($A$1:$E$5=LARGE($A$1:$E$5,ROW(A1)))*COLUMN($A$1:$E$5)-MIN(COLUMN($A$1:$E$5))+1))

Largest 10 Companies array enter and drag down 9 rows
=INDEX($F$2:$F$5,MAX(($A$2:$E$5=LARGE($A$2:$E$5,ROW(A1)))*ROW($A$2:$E$5)-MIN(ROW($A$2:$E$5))+1))

Having done these 2 I think you should be able to fathom out how to do the
MIN 10 but if you struggle then post back.

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike
 
S

SAM

mike...you are a legend.

Mike H said:
Sam,

These formula look familiar, I think I did these a couple of days ago!!

Anyway, you want the largest 10 shops and companies:-

Largest 10 shops array enter (see below) and drag down 9 rows.
=INDEX($A$1:$E$1,MAX(($A$1:$E$5=LARGE($A$1:$E$5,ROW(A1)))*COLUMN($A$1:$E$5)-MIN(COLUMN($A$1:$E$5))+1))

Largest 10 Companies array enter and drag down 9 rows
=INDEX($F$2:$F$5,MAX(($A$2:$E$5=LARGE($A$2:$E$5,ROW(A1)))*ROW($A$2:$E$5)-MIN(ROW($A$2:$E$5))+1))

Having done these 2 I think you should be able to fathom out how to do the
MIN 10 but if you struggle then post back.

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike
Click to expand...
 
S

Shane Devenshire

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.
 
R

RagDyeR

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!
=====================================================

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
 
S

Shane Devenshire

Hi again,

I see they would be even more consistant if I reversed two of the arguments
in the 3rd formula:

=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((A2:E5=MIN(A2:E5))*ROW(A2:E5)))
=INDEX(A1:E1,MAX((A1:E5=MIN(A1:E5))*COLUMN(A1:E5)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP/MATCH Query - quite complex....but interesting. 3
Conditional Formating: MAX & MIN 3
Pivot Table Summarizing Quirk - Please Help 1
help with index to return particular cell value 0
Conditional formatting Max to a entire row A1:D1 with E1 being Ma 2
ScrollBar - set MIN/MAX cell value 2
Changing entire row based on 1 MAX cell value (Conditional Formatt 2
Question about using MIN/MAX, INDEX with Date range 3

Top