Display date above the left-most cell that contains a certain letter/number combination?

[ntech]

This is an example of a formula I would like to house in the AC4 cell that will display the date above the left-most cell that contains:

Any number from 0-999

Any number/”C” combination such as:

0-9C
10-99C
100-999C


I would like the formula to exclude the “CO” code and any other character only combinations.

 

[Aladin Akyurek]

Control+shift+enter:

=IFERROR(INDEX($G$2:$R$2,MATCH(TRUE,ISNUMBER(SUBSTITUTE(UPPER(G4:R4),"C","")+0),0)),"does not exist")

 

[ntech]

Thank you, that works great!

Control+shift+enter:

=IFERROR(INDEX($G$2:$R$2,MATCH(TRUE,ISNUMBER(SUBSTITUTE(UPPER(G4:R4),"C","")+0),0)),"does not exist")

 

[Aladin Akyurek]

Thank you, that works great!

You are welcome.

 

[ntech]

=IFERROR(INDEX($G$2:$R$2,MATCH(TRUE,ISNUMBER(SUBSTITUTE(UPPER(G4:R4),"C","")+0),0)),"does not exist")

Is there a way to modify the above formula so it will still calculate the left-most date when the following data is entered:

100/100C (both lowercase and uppercase "c")

100C/200C (both lowercase and uppercase "c")

100C/200C (both lowercase and uppercase "c")


The current formula will not function properly with the above combinations in the cells.

Thank you, that works great!

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($G$2:$R$2,MATCH(TRUE,ISNUMBER(SUBSTITUTE(IF(ISNUMBER(SEARCH("/",G4:R4)),REPLACE(G4:R4,1,SEARCH("/",G4:R4),""),IF(G4:R4="","",G4:R4)),"C","")+0),0)),"does not exist")