# Excel formula question

October 19, 2011 at 09:39:40
Specs: Windows 7
 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!

See More: Excel formula question

#1
October 19, 2011 at 10:02:05
 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 n ```Then in cell C1 enter the formula:=VLOOKUP(MIN(A1:A6),A1:B6,2,FALSE)EDITAfter re-reading your post, not sure this is what you want.MIKE

Report •

#2
October 19, 2011 at 10:26:05
 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!

Report •

#3
October 19, 2011 at 10:36:08
 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)MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
October 19, 2011 at 14:42:39
 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

Report •

#5
October 19, 2011 at 15:04:30
 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.MIKEhttp://www.skeptic.com/

Report •

#6
October 19, 2011 at 15:41:28
 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.MIKEhttp://www.skeptic.com/

Report •

#7
October 19, 2011 at 15:57:56
 Not a problem at all, thanks for all of your help on this. I really appreciate it.

Report •

#8
October 20, 2011 at 06:13:19
 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.

Report •

#9
October 20, 2011 at 12:21:41
 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.MIKEhttp://www.skeptic.com/

Report •

#10
October 20, 2011 at 12:42:18
 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.

Report •

#11
October 20, 2011 at 15:45:27
 DerbyDad thank you. very much appreciated.

Report •