Thread
:
VLOOKUP in a RANGE.
View Single Post
#
7
Posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
Posts: 10,124
VLOOKUP in a RANGE.
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"SAM" wrote in message
...
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" wrote:
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" wrote:
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" wrote:
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" wrote:
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett