Prev: Calendar Formatting Based Upon Multple Date Ranges
Next: Determining number of entries in a table
From: Mike H on 7 Mar 2010 15:09
Thanks for that Biff, I never tested it properly.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"T. Valko" wrote:

> >=INDEX(A1:A5,MATCH(1,("x"=D1:D5)*(MIN(IF(E1:E5>0,E1:E5))=E1:E5),0))
>
> That will return an error when the min number in the range is not associated
> with "x" and is less than the min number that is associated with "x".
>
> A......64
> B......16
> C..x..89
> D......5
> E..x...16
>
> Based on that sample data the above formula returns #N/A. The correct result
> should be E.
>
> Try this (array entered):
>
> =INDEX(A1:A5,MATCH(1,(D1:D5="x")*(E1:E5=MIN(IF(D1:D5="x",E1:E5))),0))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
> news:671A238D-B8FE-4EFD-AB5C-BFEDDAFC04AB(a)microsoft.com...
> > Hi,
> >
> > Try this ARRAY formula
> >
> > =INDEX(A1:A5,MATCH(1,("x"=D1:D5)*(MIN(IF(E1:E5>0,E1:E5))=E1:E5),0))
> >
> > 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
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "jtfalk" wrote:
> >
> >> A B C D
> >> E
> >> First x
> >> 5
> >> second x
> >> 0
> >> third x
> >> 2
> >> fourth x
> >> 2
> >> fifth x
> >> 1
> >>
> >> I am trying to get the A name with criteria of D and the lowest number.
> >> This
> >> list is about 100 items. So in the above case is would look through all
> >> of D
> >> for x's and the lowest E value which is 1 above. I was trying this:
> >> =INDEX(A1:A100,MATCH(MIN(E1:E100),G2:G19,0)*(D1:D100="x"),0)
> >> The problem is it looks at the zero and returns second
> >>
> >>
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Calendar Formatting Based Upon Multple Date Ranges
Next: Determining number of entries in a table