Return a portion of text in a CHILD cell based on Date column

Beth C

I am trying to automatically pull an airport tricode out of text in a child cell (Delivery Location) based on the most recent Delivery Date. The green row is the parent. Is this possible?

Paul Newcome

Try something like this...

=IF(CONTAINS("SOS", INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0))), MID(INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0)), 5, 3))

Beth C

I should add that if the most recent "Delivery Location" does not include SOS, the returned data should be blank.

Paul Newcome

What exactly are you wanting to pull from which cell?

Beth C

I want to pull text out of the most current "Delivery Location" based on the "Delivery Date" child. If the most recent "Delivery Date" Contains "SOS", I want to pull out characters 5-7 ("DFW" out of "SOS DFW Hold"). If "Delivery Date" does not contain "SOS", the cell should be blank. I want this text to appear in the parent row (the green row) in the "Delivery City" cell.

In my screen grab, "DFW" would automatically populate in the green cell where it is manually entered now based on the "SOS DFW Hold" in the child row that shows 3/4/22 as the "Delivery Date".

I think I can figure out how to get characters 5-7 out of the text. It's getting the "Delivery Location" based on the "Delivery Date" that is stumping me.

Paul Newcome

Try something like this...

=IF(CONTAINS("SOS", INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0))), MID(INDEX(CHILDREN([Delivery Location]@row), MATCH(MAX(CHILDREN([Delivery Date]@row)), CHILDREN([Delivery Date]@row), 0)), 5, 3))

Beth C

That works. Thank you!

Paul Newcome

Happy to help. 👍️