Forum Discussion
Solved
Providing an Availability Date
Hi, I have a task to provide the availability date for confirming Customer Orders. I have built an initial excel table with all the Demand (Customer Sales Orders) and Supply (Purchase Orders), t...
something like this?
The main formula is:
=INDEX(
$C5:$H$30,
MATCH(
0,
FILTER(
$H5:$H$30,
$C5:$C$30=C5
),
0
),
2
)
The rest of the formula is just to format the date
cheers
Yea_So
Many thanks for this and the rapid reply - it works really well, and the use of FILTER in the INDEX/MATCH is really clever.
As I mentioned in the original post, the result table is coming from a pivot table, so the length of the table can vary; is there a possibility to modify the formula so that it is dynamic for the last row (particularly if the result table is extended)?
Many thanks once again for your help.
Cheers
Many thanks for this and the rapid reply - it works really well, and the use of FILTER in the INDEX/MATCH is really clever.
As I mentioned in the original post, the result table is coming from a pivot table, so the length of the table can vary; is there a possibility to modify the formula so that it is dynamic for the last row (particularly if the result table is extended)?
Many thanks once again for your help.
Cheers
If its an excel formatted table, the formulas should propagate dynamically (this is a conjecture on my part since I've seen it happen) test it and let me know so we can figure it out together.
- Yea_So
Sorry, my bad. It's not a table, as I am calculating directly from the pivot table result.
Hope this helps to understand the reason for the "growing" table length.
Cheers