Lookup Last Value then Generate Next Alphanumeric Value

Hi All

I've got to develop a formula that does the following:

1. Concatenates the values I specify in cells H6:K6
2. Using this concatenated value, find the last 'similar' number in Column F
3. Increment the last value by 1

Essentially I am creating a sequential number generator that would allow the user to limit the searching/filtering effort by giving them the next available number based on the values they choose from the drop down menus (H6:K6). One twist is that provisions need to be made to accommodate for an extra letter at the end of the number i.e. 11000A, 11000B.

The attached should make it a lot clearer. Any help would be greatly appreciated.

number generator.xls

find attached!

if this helps Click "*" add rep icon in the bottom left corner of my post!