formula help with text "yes, "no" already have formula just need it revised

[steve89]

hello i have this formula

=IFERROR(IF($BQ$24=13,IF($BL$26=0,"yes","no")&TEXT($BL$26/BL$26,),"NO"),"yes")

i want the formula to know the following because it works to an extent
if BL26 = 0 then "NO"
if BQ24 = 13 then "YES"


thank you

 

[steve89]

unbelievable thank you so much PERFECTLY EXECUTED thanks i have one last request i posted something previously that is as follows

=IF(COUNTIF(C13:AG24,"eg/ig")>0,TODAY(),"")

the point is to return the date this occured based off the calendar its on and the calendar is as follows:
C11:AG11 = DAYS IN THE MONTH BY # FORMAT
B12:B23 = MONTHS IN THE YEARS
C13:AG24 = CONDITION THAT I ENTER AND IN THIS CASE IM LOOKING FOR WHEN "EG/IG" OCCURRED.

hope you can help - its work with that formula but returns today's date

 

[steve89]

https://www.mrexcel.com/forum/excel-questions/1045879-return-date-if-calendar-equals-eg-ig-2.html

link to original post

 

[dreid1011]

unbelievable thank you so much PERFECTLY EXECUTED thanks i have one last request i posted something previously that is as follows

=IF(COUNTIF(C13:AG24,"eg/ig")>0,TODAY(),"")

the point is to return the date this occured based off the calendar its on and the calendar is as follows:
C11:AG11 = DAYS IN THE MONTH BY # FORMAT
B12:B23 = MONTHS IN THE YEARS
C13:AG24 = CONDITION THAT I ENTER AND IN THIS CASE IM LOOKING FOR WHEN "EG/IG" OCCURRED.

hope you can help - its work with that formula but returns today's date

You're welcome. I am glad that worked for you.

As for this one, it looks like it will always return today's date if it finds at least 1 occurrence of "eg/ig". Are you trying to return the actual date that "eg/ig" occurred instead of today's date?

 

[steve89]

thanks for the prompt reply yes im trying to return the date this occurred

 

[dreid1011]

thanks for the prompt reply yes im trying to return the date this occurred

How many times can eg/ig occur and which instance do you want to find?

 

[steve89]

can only occur one time between cell C12:ag23

 

[dreid1011]

Ok, I assumed month names in B12:B23. Give this a try. It's not returning a number though, so it may need to be tweaked if you are using the result in another formula.

=INDEX(B12:B23,SUMPRODUCT(--(C12:AG23="eg/ig")*ROW(C12:AG23))-11)&" "&INDEX(C11:AG11,,SUMPRODUCT(--(C12:AG23="eg/ig")*COLUMN(C12:AG23))-2)

 

[steve89]

so it works but not returning the dates returning this "431321" but format is entered as date hmmm trying to think here

 

[dreid1011]

Well, I typed in Jan, Feb, Mar etc and 1, 2, 3, 4, 5 etc. So the formula is putting the two together like Jan 5 as a text string. How are your headers (months and days) formatted?

 

[dreid1011]

You could try wrapping the formula in =DATEVALUE( formula )