Search Cells for given text and return closest date in row below

Hi,

For a set product I have a criteria section in cells D2 to ABC2, these are COMPLETE, UNDER TEST, NOT STARTED/SAMPLED. In Cells D5 to ABC5 I have estimated completion dates. I want to determine the next date that a sample categorised as "UNDER TEST" will be completed.

I have tried the below formulas which don't return the correct answer.

=MIN(IF(D2:ABC2="*UNDER TEST*", ABS(D5:ABC5-TODAY()), ""))

=INDEX(D5:Z5,MATCH(MIN(IF(D2:ABC2="UNDER TEST",IF(ISNUMBER(DATEVALUE(D5:ABC5)),ABS(DATEVALUE(D5:ABC5)-TODAY())))),IF(D2:ABC2="UNDER TEST",IF(ISNUMBER(DATEVALUE(D5:ABC5)),ABS(DATEVALUE(D5:ABC5)-TODAY()))),0))

=XLOOKUP("UNDER TEST",D2:ABC2,IF(ISNUMBER(DATEVALUE(D5:ABC5)), ABS(DATEVALUE(D5:ABC5)-TODAY()), ""),"INCORRECT",1,2)

Any help would be greatly appreciated

Example of data, next available will be on a separate worksheet

This is not clear. Where on your sample is your mocked up results? In which cell are you wanting a formula - A9?
In words, not formula, explain how to get the answer you're looking for.

In cell A9 I want a formula to display the ETA date the next sample currently categorised as "UNDER TEST" will become available. So the formula will search through row 2 and only look for UNDER TEST columns then compare all their ETAs in row 4 and return the ETA which is the closest to today's date. In the example attached I want the formula to return "26/02/23".

Try this in A9:

=LET(
UnderTestDates,FILTER(D4:H4,D2:H2="Under Test"),
DayDiffs,ABS(TODAY()-UnderTestDates),
MinDayDiff,DayDiffs=MIN(DayDiffs),
INDEX(UnderTestDates,,MATCH(TRUE,MinDayDiff,0)))

Please Login or Register  to view this content.