Lookup Function

[billykiller05]

I need help with a function. I have a list (see below) which I am needing to setup a formula for. I need a function which will look at a number and return values from this list. All of my group heading end in -00 and the sub groups under this heading begin with the first two digits of the group heading. For example Barski is 60-00 while Jones is 60-01, Damon is 60-02 etc. I have quite a few headings and then there are several sub groups for each heading. I would like to enter 60-00 and the function would return the first of the sub groups (60-01) and then the second and so forth.

50-00 Will
50-01 Stamps
50-02 Drinks
50-03 Plates
60-00 Barski
60-01 Jones
60-02 Damon
60-03 Adams

[lazyme]

How do you want your results to look like?

For example, when you enter 60-00, do you want your results to show in seperate columns in 1 row or concatenated in 1 cell?

[billykiller05]

I would like for the results to be in seperarte cells. I have a VLookup Function so when I enter the original code it will return the name I would like to set up the sub groups the same way. So if I enter a value in cell A2 (60-00) then the VLookup will return the Name (Jones) in cell B2. I need a function or functions which would then return all of the sub groups (60-01, 60-02, etc) for the first two heading digits (60) in cell C2, E2, etc and I could have Vlookups in the cells D2, F2, etc to return the names for each sub group under the heading.

[Paul]

Hi billykiller,

Do you always have one header (50-00, etc.) and 3 subheaders (50-01, 50-02, 50-03)? Or are there a varying number of subheaders?

If there are always three subs, then you could use this formula in C2, E2 and G2:

Please Login or Register  to view this content.

Where P1:P8 is where your list of codes reside (and presumable Q1:Q8 has the names/descriptions). Adjust the formula to suit your needs.

Getting really lazy, if there are always 3 subs, and if they're always 01, 02 and 03, then C2, E2 and G2 could simply be:

Please Login or Register  to view this content.

If your data isn't like any of this, well, there's other ways to get what you need.

[billykiller05]

There are not always just three sub groups for each main group I have included an excerpt from the list. The numbers vary but the sub groups always begins with the first two digits of the main group's number.


70-00
70-02
71-00
71-01
71-03
71-04
72-00
72-02
72-03
72-06
72-07
72-09
72-10
72-11
72-12
72-14
72-15
72-16
73-00
73-01
73-02
73-15
73-17
74-00
74-01
74-02
74-04
74-05
75-00
75-01
75-02
75-03
75-04
75-06
75-07

[billykiller05]

Can someone tell me if this is possible to do?