Looking up value from table with ranges to give output

[fmemon]

Hi,

I'm trying to get some help creating a formula that looks up a value located on a table and returns a value based on parameters in a different table. I've found formulas online using INDEX and MATCH, but I can't quite figure out how to make it work for my purposes.
I've attached a file with some example tables of what I'm looking at doing.

The left most table, named Task_table, currently has a list of four generic tasks, each with its own projected completion date. The right side table, named Iteration_table, contains start/end dates for some "iteration loops".

What I want to do is to populate column A with the iteration numbers based on projected completion dates. However, if the date is outside the bounds of the iteration loops already defined, it should come back with an error message (for example: say "undefined").

I've added some comments to my attachment showing where the formula needs to be and where the data is being pulled from.

Let me know if you have any questions and thanks for any help you can provide.

[Estevaoba]

Try

Please Login or Register  to view this content.

Good luck!

[fmemon]

That works perfectly.

Thank you very much!

[Estevaoba]

You're welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day!

[fmemon]

Need some more help on this one.

The values that come out of the formula provided are coming up as text values. Is there a way to get the same result but have the values output as actual numbers?

[AliGW]

Try this:

=--IFERROR(TEXTJOIN("; ",1,FILTER(Iteration_table[Iteration],(Iteration_table[Start Date]<=[@[Required completion date]])*(Iteration_table[End Date]>=[@[Required completion date]]))),"Undefined")

or:

=IFERROR(TEXTJOIN("; ",1,FILTER(Iteration_table[Iteration],(Iteration_table[Start Date]<=[@[Required completion date]])*(Iteration_table[End Date]>=[@[Required completion date]]))),"Undefined")+0

or:
=VALUE(IFERROR(TEXTJOIN("; ",1,FILTER(Iteration_table[Iteration],(Iteration_table[Start Date]<=[@[Required completion date]])*(Iteration_table[End Date]>=[@[Required completion date]]))),"Undefined"))