Lookup function needed?

Hi guys,

This is an odd one, I hope you can help with. I'm not sure if it needs a lookup code or what...

See attached spreadsheet.

In the column 'serial no', we can get any number of different results, but these always start with a two or three letters, which I have a list of - there's only about ten different possibilities (i./e. VC, DAF, CN etc etc). The digits that follow these two/three letters are always whole integers, which just progress sequentially, as in the example attached.

What I need is a cell (to the side of the main data), which shows the last VC, DAF, CN number in the list... i.e. in the attachment, for VCs we've got up to VC442168, and for CNs we've also got up to CN510240. So I'd like to be able to make a box which in logical steps goes something like; Look in column C, for codes beginning with VC, look for last instance of this VC in the column, and display this in cell L39 (for example, could be any cell I choose). If the code was working correctly for VCs, it would display VC442168 in cell L39.

I'd then want to do a similar code for DAF, CN, etc etc, but this will produce in a separate box (i.e. L40, L41, L42), so I'll just copy the code above replacing the letters with DAF instead of VC, and so on and so forth.

Is there a way of getting it to do this?


thanks for your help!

Km

That, is, amazing. Better answer than I was expecting. Thank you!

Thankfully that won't matter - the first letter stays the same for a looooong time, years, and the second/third letters don't matter so much as the first letter is the one which defines what that serial number is in respect of. So this will work perfectly for my purposes.