Hi, I have two columns in my worksheet one with Y's and N's to indicate yes's and no's and the other with corresponding dates. I need a formula that will look in to the the Y/N column and extract the earliest date associated with Y. I thought combining a sumproduct and an if statement in some way might solve the issue but that hasn't worked. Am I going about this all wrong or do I need to do something different? Thanks!

Provided your data looks like this: A B 1) 10/3/2011 y 2) 10/4/2011 n 3) 10/5/2011 y 4) 10/2/2011 n 5) 10/7/2011 y 6) 10/8/2011 nThen in cell C1 enter the formula:

=VLOOKUP(MIN(A1:A6),A1:B6,2,FALSE)

EDIT

After re-reading your post, not sure this is what you want.

MIKE

Thanks Mike, So this vlookup returns the the earliest date in column A regardless of whether or not the corresponding cell in column B has the "y" or "n" value. I am looking to return the earliest date from column A related to any cell in column B with only "y" values and not "n" values. Sorry if this was confusing earlier.

Thanks!

OK, I kinda thought I got it wrong.

Try this, same setup as above:=SUMPRODUCT(--(A1:A6=MIN(A1:A6)),--(B1:B6="Y"),A1:A6)

MIKE

Thanks Mike, The sumproduct is pulling in 0 for the value instead of the earliest date for "y". Anything i could be doing wrong?

Thanks

Sorry, still not right. It's finding the lowest date,

but if the adjacent cell is not a Y then it returns zero.I'll work on it.

Again, sorry.

MIKE

I don't think your problem can be solved using a Formula, as you need to step thru each date until you find the lowest date with a Y and I can't think of any way to accomplish this using just a formula. You may need a VBA solution and unfortunately my VBA skills are nil.

Hopefully someone else will be able to assist.

Sorry.

MIKE

Not a problem at all, thanks for all of your help on this. I really appreciate it.

Try this array entered formula: =MIN(IF($B$1:$B$6="y",$A$1:$A$6))

Enter the formula using Ctrl-Shift-Enter which will put brackets around the formula. Typing the brackets will not work.

{=MIN(IF($B$1:$B$6="y",$A$1:$A$6))}

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03, nice solution, sorry I didn't think of it myself.

but my computer crashed yesterday and I've been a bit distracted trying to get it back on-line.

Working of a small laptop can be annoying.But, still a great answer.

MIKE

re: "sorry I didn't think of it myself"Google is our friend. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad thank you. very much appreciated.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History