Hello,
I have a column full of titles that look like this:
I am looking for a formula that will search through column A looking for a specific set of words such as "CAB 3" or "CAB 4" then look at the number just after the word "PED" and provide the next available number i can use to name the next "PED". So if, in this case, "PED 3110 CAB 4" the formula would only look in the "CAB 4" 's and return the next available number which would be "3111". I can create a separate cell for each search if need be. I started with this but it does not work:
=IF(ISNUMBER(SEARCH("CAB 3",A:A)),IFERROR(INDEX(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))),MATCH(0,INDEX(COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))),),0)),MAX(A:A)+1),"")
Thanks in advance!
I have a column full of titles that look like this:
Ped 3081 CAB 3
|
=IF(ISNUMBER(SEARCH("CAB 3",A:A)),IFERROR(INDEX(ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A))),MATCH(0,INDEX(COUNTIF(A:A,ROW(INDIRECT(MIN(A:A)&":"&MAX(A:A)))),),0)),MAX(A:A)+1),"")
Thanks in advance!