Lookup one value and return multiple corresponding values

[Guest]

Hi - I have tried some formulas but not really sure what I'm doing so need
help please.
I want to be able to find the next available sequential number for a new
part from a list. This number is digits 3-5 of a code. e.g. 3R001A. Parts can
start with a variety of numbers/letters depending of processes and materials
e.g. 1-5, F/N/X/D/ E/P/R/H/B, followed by 3 digit sequential code. I want to
be able to look up say all codes starting with '3R' and find the next
sequential number available for new part.
I have an exported list of all current parts. I thought I could do this by
using lookup function unless any one knows of a better way and retuning
multiple corresponding values and then adding 1.
Many thanks,
Carrie

 

[Guest]

Data > Filter > Autofilter > Custom > Begins with

 

[Guest]

Hi thanks for quick response. This does get information I need but I want to
return information so that when someone selects characters 1 & 2 for code
from validation lists e.g. 3 R then it automatically tells them next
available number for that type of part. Those first two characters can be
different each time.

 

[Guest]

What you need is easy if the column of parts were inverted. We could use a
formula with VLOOKUP Is this approach O.K.??

 

[Guest]

Hi - I have managed to do it with a Vlookup by sorting parts in descending
order so it picks up first in list which is newest and then used MID to
extract numbers and add 1.
Manythanks for your help.