VLOOKUP in a RANGE.
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
|