VLOOKUP in a RANGE.

[SAM]

Would really appreciate any ideas...

I have a range of data A1:M20 containing a number of different percentages.
I want to look up the max and min and get it to report the corresponding name
in column N (the column to the right of the range).

The Max/Min can be in any column or row so the v look up needs to be dynamic
or be able to look up the max/min in a range...

please please help.....much apprecitaed

 

[Mike H]

Hi,

Try these 2 array formula

Max

=INDEX(N1:N20,MAX((A1:M20=MAX(A1:M20))*ROW(A1:M20)-MIN(ROW(A1:M20))+1))

Min

=INDEX(N1:N20,MIN(IF(A1:M20=MIN(A1:M20),ROW(A1:M20)-MIN(ROW(A1:M20))+1)))


This is an array formula which must be entered by pressing 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 formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[SAM]

it doesn't seem to have worked....can i send you something - it might make
more sense...i still get a ref# error.

 

[Mike H]

Sam,

I prefer to keep postings in the group. You can post some sample data here
or upload your file to the link below (It's free) and I'll take a look.

http://www.savefile.com/

Mike

 

[SAM]

Okay
well here is a sample range

A B C D E F
1 0.5% 0.23 % 0.09% 0.10% 0.69% vodafone
2 0.5% 0.85% 0.05% 0.26% 0.95% tesco
3 0.93% 0.86% 0.94% 0.46% 0.32% c&A
4 0.19% 0.73% 0.56% 0.47% 0.46% dixons


I need a formula that scans the percentage data for the max value and then
reports the corresponding company in colum f. The percentage change all the
time so the company might change..


thank you so much.

 

[Mike H]

Sam,

The formula I gave you work prefectly on this data and here are the 2
formula modified for the ranges in your sample data. Note that Tesco is both
the Min and the Max so both formula return Tesco but if you enter another max
then the formula update. VERY IMPORTANT, read the array formula instructions
below.

MIN
=INDEX(F1:F4,MIN(IF(A1:E4=MIN(A1:E4),ROW(A1:E4)-MIN(ROW(A1:E4))+1)))

Nax
=INDEX(F1:F4,MAX((A1:E4=MAX(A1:E4))*ROW(A1:E4)-MIN(ROW(A1:E4))+1))

This is an array formula which must be entered by pressing 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 formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Don Guillett]

I just tested Mikes formula, modified to your sample and got the correct
result.
=INDEX(F1:F20,MAX((A1:E20=MAX(A1:E20))*ROW(A1:E20)-MIN(ROW(A1:E20))+1))
Did you remember to ARRAY enter

 

[SAM]

Okay - WOOHOOO it works.....guy's i have literally been trying to do that for
days....

next question:

if i also had different labels across the top:

so far i can find the min and max for the column f...but would it be posible
to also find the column header/label with a sepearte formula

basically so that i can say that for the MIMIMUM in the range: it's e.g
tesco and shop 1.


2. Instead of the max/min - can this be done for the top five/bottom
five.....for this do i just do max-1?

 

[Don Guillett]

A bit of thinking would have given you the answer.
=INDEX(1:1,MAX((A1:E20=MAX(A1:E20))*COLUMN(A1:E20)-MIN(COLUMN(A1:E20))+1))

 

[SAM]

sorry i don't quite get that formula - is that to run over my sample data
below?