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's new Surface Go will be a hit?

Discuss in The Lounge

Poll History