This looks like a good solution. I shall go away and put it into practise.

Many thanks to all
--
cangiff


"T. Valko" wrote:

OK, you can build 2 tables and **they must be sorted in ascending order on
the left column**.

Build one for the number codes and a separate one for the alpha codes. Use
the *lower boundary* of each sorted sequence for the interval.

...........A..........B
1......100.......Y02
2......111.......Y01
3......121.......Y03
4......131.......Y02
5......141.......Y03

..........D..........E
1....AAA.....Y01
2....AAF.....Y03
3....ABB.....Y02

Then, with cell G1 as the input cell:

G1 = 118

=VLOOKUP(G1,IF(ISNUMBER(G1),A1:B5,D1:E5),2)

Returns: Y01

I can see where it would be a pita to build the table for the alpha codes!


--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
Thanks for this. I can see that this will work, and I may have to go down
this route.

However, I forgot to mention that although there are only 100 parent
codes,
there are in excess of 5000 child codes. Some of the parents are linked
to
in excess of 400 child codes. Children codes a frequently added to and
infrequently removed. I was hoping that I could maintain a table that had
ranges of child codes instead of detailing individual codes as the
resultant
table would be more difficult to maintain.
--
cangiff


"T. Valko" wrote:

Create a 2 column table with the cild codes in the left column and the
corresponding parent codes in the right column:

...........A..........B
1......100.......Y02
2......101.......Y02
3......ABB.....Y02
4......111.......Y01
5......AAA.....Y01

Fill in *all* of the codes.

Then...

D1 = input cell = 111

Formula in cell E1:

=VLOOKUP(D1,A1:B5,2,0)

--
Biff
Microsoft Excel MVP


"cangiff" wrote in message
...
I am trying to do something that I am sure is very simple to achieve,
but
it
is beyond my limited knowledge i.e.

I have a list of 100 3 digit codes that can be alpha/numeric.

Each code represents the aggregate of of another series of 3 digit
codes
that can also be alpha/numeric e.g.

Parent Y01 is total of children codes in ranges 111 - 120, AAA-AAE
Parent Y02 is total of children codes in ranges 100-110, 131-140,
ABB-ABF
Parent Y03 is total of children codes in ranges 121-130, 141-150,
AAF-ABA

I would like a formula that will return which parent code applies for
any
given child code i.e.

Input: Child code = 111
Output: Parent code = Y01

Can anyone suggest a solution?

cangiff