Prev: Calendar Formatting Based Upon Multple Date Ranges
Next: Determining number of entries in a table
From: jtfalk on 7 Mar 2010 04:00
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


From: jtfalk on 7 Mar 2010 04:19
I got it close but it always looks for the lowest number which I need it to
look for the lowest number that has an "x" in that row:
=INDEX(A1:A100,MATCH(MIN(E1:E100)&"x",E1:E100&D1:D100,0))

"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
>
>
From: Mike H on 7 Mar 2010 04:49
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
>
>
From: jtfalk on 7 Mar 2010 13:26

Okay - it is now working.
Is there a way to use this can have it rank them in order of the lowest to
the highest? So the list would take the 2 D's and put them in order in
different cells in the lowest to highest order?

"Mike H" wrote:

> 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
> >
> >
From: T. Valko on 7 Mar 2010 13:44
>=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
>>
>>


 |  Next  |  Last
Pages: 1 2
Prev: Calendar Formatting Based Upon Multple Date Ranges
Next: Determining number of entries in a table