If true then show why?

[screach]

Ok really wasn't sure how best to title this.

You guys have been a huge help in learning how to manipulate excel to get what I want out of it. So here is the question I am wrestling with now.

I have a formula (thanks to your help) that looks at various data from sheet 1 and looks for 3 data points to match in sheet 2 to determine if a particular row is True or False.

Now I am wanting to show the event name if the data points (that matched sheet 2) are true.

I know that functional arguments provide some of this functionality but since the formula is essentially the original argument i am not certain where to go next.

Should the original formula be adjusted to then give a response other than True/False to show the event name, or should I use the result of the original formula to then write another formula to look at if true (then why true & if why then show event in row of why)... Spent a few hours on this already and my head is killing me Any help from the guru's is greatly appreciated.

Now I am second guessing using excel...a database would have been easier than this...then again I am learning something new so i'll carry on.

 

[Jake Collins]

Dear Screach

Why does the formula in J2 refer to D213,F213 and I213 and not D2, F2 and I2?

Assuming it should be the latter cells then the following array formula works.

=IFERROR(INDEX(TVA!$I$2:$I$1877,SUM((IF(D2=TVA!$A$2:$A$1877,ROW(TVA!$A$2:$A$1877),0))*(I2=TVA!$H$2:$H$1877)*(F2>TVA!$C$2:$C$1877)*(F2<TVA!$D$2:$D$1877))-1),"")

 

[bosco_yip]

Try,

1] In J2, formula copy down :

=COUNTIFS(TVA!$A$2:$A$1877,D2,TVA!$H$2:$H$1877,I2,TVA!$C$2:$C$1877,"<="&F2,TVA!$D$2:$D$1877,">="&F2)>0

2] In K2, formula copy down :

=IFERROR(LOOKUP(2,1/((TVA!$A$2:$A$1877=D2)*(TVA!$H$2:$H$1877=I2)*(TVA!$C$2:$C$1877<=F2)*(TVA!$D$2:$D$1877>=F2)),TVA!I$2:I$1877),"")

Regards
Bosco

 

[screach]

Perfect! Thank you big time. bosco_yip