Formula that Returns a Value under Specific Conditions

[silverlucky5]

My problem is that I have some data that was dumped from a database and I need to return values from that data under specific conditions (shown in attachment). Any help you can provide will be greatly appreciated! Thanks!

[RaulSerg]

Sorry, your instructions are not clear to me.
What does mean: If Void = Y, then no need to look further? Look further to do what exactly?
And what shall the input be in such case?

See if the attached is what you have in mind (I think your result in C7 is wrong?)

Regards

[silverlucky5]

Wow that was SO fast I have attached a new version of file...please see blue text in attached. Thank you SO much...it does appear to do exactly what I need it to....

[Gregb11]

I'm unclear why you have the results you do. For row 4, why is that "Voided Record"? The date is Feb 1, 2020, which is between Jan 1, 2020 and Dec 31, 2020, which is row 15 which matches the account # of ABC and has a span of 366 and is not voided. So why isn't the answer for Row 4 "Sacramento"?

Also, why is row 7 "Voided Record"? Account is "DEF" and date is Feb 1, 2019, which equals the account and is between the dates in row 18, where void = "N", so why isn't row 7 Region = "Sarasota"?

In C2, I entered this formula and got all the answers that I assumed were correct but didn't match yours:

=IFERROR(IF(INDEX($B$13:$B$20,MATCH(1,($A$13:$A$20=A2)*(B2>=$D$13:$D$20)*(B2<=$E$13:$E$20),0))="Y","Voided Record",INDEX($F$13:$F$20,MATCH(1,($A$13:$A$20=A2)*(B2>=$D$13:$D$20)*(B2<=$E$13:$E$20),0))),"No matching span w/in account")

Now, this is only for Part 1. I haven't tried tackling Part 2 because I'm not sure if I'm on the right track for Part 1.

[RaulSerg]

@Greg11b: my understanding is that row 4 is voided and not Sacramento because it would duplicate the outcome from row 3.
Row 7 was indeed a mistake as was clarified above (or rather in the updated workbok) and should be Sarasota as you suggested.
Row 9 is then voided again because duplicate.

I agree that the data is not really clear, e.g. there's these span durations of -30 days and only these are marked with Void = Y which seems to make either the Y/N or <=365/366 tests redundant; it does look to me as either a too small sample or not the cleanest data to work with.

[silverlucky5]

Hi...so it seems, based on the two responses, that the voids are causing a problem...So, I was thinking, I will just eliminate the void records from my query of the data so no voided records will be included in the database data at all. Am attaching a revised example that I hope makes more sense!

[RaulSerg]

Sorry, I am not clear whether your questions is answered or whether the alterntives provided did in fact NOT give you the solutions you were looking for?

[silverlucky5]

Hi, thanks for checking... No, question not answered yet...I think my original data was causing confusion so I added new example in latest attachment (version 4). I think I need to use a combiniation of Index and Match functions perhaps...

[Gregb11]

Put this in C2 and copy it down. It gives me all the same answers. see spreadsheet.

=IFERROR(INDEX($E$13:$E$18,MATCH(1,($A$13:$A$18=A2)*($B$13:$B$18>=365)*($B$13:$B$18<=366)*(B2>=$C$13:$C$18)*(B2<=$D$13:$D$18),0)),IFERROR(IF(INDEX($A$13:$A$18,MATCH(1,($A$13:$A$18=A2)*(B2>=$C$13:$C$18)*(B2<=$D$13:$D$18),0)+1)=A2,INDEX($E$13:$E$18,MATCH(1,($A$13:$A$18=A2)*(B2>=$C$13:$C$18)*(B2<=$D$13:$D$18),0)+1),"no match found"),"no match found"))

[bebo021999]

Another with array formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[silverlucky5]

Thank you so much! that seems to work! And now I've been given enough knowledge to be dangerous ha ha...

[silverlucky5]

Hi Bebo, yes, am familiar with array formulas...I was all around it, but just didn't know how to put it together....thanks to all for the wonderful help!