Prev: search for text then return text
Next: Count how many times conditional formatted cells are positive
From: New Hope UMC on 3 Mar 2010 14:57
I have 3 columns; Market #, Start Date and End Date
Currently when I match Market #1 in Cell A3 the following works
=INDEX(MIN(H2:H20000),MATCH(A3,P2:P20000,0))
But when I try and use it to find the same info for Market #2
=INDEX(MIN(H2:H20000),MATCH(A4,P2:P20000,0))
I get a #Ref! Error...
Tried array etc...to no avail.
Can someone figure this one out?
Thanks!
From: Domenic on 3 Mar 2010 16:05
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MIN(IF(P2:P20000=A3,H2:H20000))

--
Domenic
Microsoft MVP - Excel
www.xl-central.com, "Your Quick Reference to Excel Solutions"

"New Hope UMC" <mynewhopeumc(a)gmail.com> wrote in message
news:c8be99f4-ee3c-41ee-806f-53a079cbbd57(a)z35g2000yqd.googlegroups.com...
> I have 3 columns; Market #, Start Date and End Date
> Currently when I match Market #1 in Cell A3 the following works
> =INDEX(MIN(H2:H20000),MATCH(A3,P2:P20000,0))
> But when I try and use it to find the same info for Market #2
> =INDEX(MIN(H2:H20000),MATCH(A4,P2:P20000,0))
> I get a #Ref! Error...
> Tried array etc...to no avail.
> Can someone figure this one out?
> Thanks!

From: New Hope UMC on 4 Mar 2010 11:59
On Mar 3, 4:05 pm, "Domenic" <dome...(a)xl-central.com> wrote:
> Try the following formula, which needs to be confirmed with
> CONTROL+SHIFT+ENTER, not just ENTER...
>
> =MIN(IF(P2:P20000=A3,H2:H20000))
>
> --
> Domenic
> Microsoft MVP - Excelwww.xl-central.com, "Your Quick Reference to Excel Solutions"
>
> "New Hope UMC" <mynewhope...(a)gmail.com> wrote in messagenews:c8be99f4-ee3c-41ee-806f-53a079cbbd57(a)z35g2000yqd.googlegroups.com...
>
>
>
> > I have 3 columns; Market #, Start Date and End Date
> > Currently when ImatchMarket #1 in Cell A3 the following works
> > =INDEX(MIN(H2:H20000),MATCH(A3,P2:P20000,0))
> > But when I try and use it to find the same info for Market #2
> > =INDEX(MIN(H2:H20000),MATCH(A4,P2:P20000,0))
> > I get a #Ref! Error...
> > Tried array etc...to no avail.
> > Can someone figure this one out?
> > Thanks!- Hide quoted text -
>
> - Show quoted text -

Sometimes something SO SIMPLISTIC eludes us!!! Thanks for the help!
 | 
Pages: 1
Prev: search for text then return text
Next: Count how many times conditional formatted cells are positive