I understand if C1 is blank, return a blank. not sure the C1&Text...perhaps stating to look for text in C1..., The Max is finding the next number, the If is stating another condition (I am assuming the matching between C1 and column A in Sheet1). I don't fully understand the Left function as I have never used it before but I think it is isolating the left letter in the column (example G) and saying if it equals C1....but after that I get lost.... Never used MID before either and definitely don't understand the C1<>"B" or why do we use absolute values (cntrl+Shift+enter).
Anyway I doubt you have time to elaborate. Thank you for providing the formula, I hope to fully understand this all one day.
For future readers of this thread, Bosco's formula above will only work on Excel 2013 or later... if you are using Excel 2010 (like I am) or earlier, his formula will generate a #NAME ? error (the IFNA function will be the cause).
I assume the 'shorter' comment relates to the formula in post 13 as the subsequent formulas take into account the multiple sheets. Whilst it is (slightly) shorter, it is only shorter because the whole of column A is referenced instead of just A1:A22 and whilst it does shorten the formula it also increases the calculation time more than 50-fold by my measurement.
I suggest another non-array & even shorter formula (still on 1 sheet to compare with posts 13 & 18) that also calculates at a comparable speed to post 13.