Lookup formula required
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 |
Lookup formula required
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 |
Lookup formula required
On Sheet2, starting in A1 make a table like this. Let's say it end at row
200 100 Y02 101 Y02 102 Y02 103 Y02 104 Y02 105 Y02 106 Y02 107 Y02 108 Y02 109 Y02 110 Y02 ABB Y02 ABC Y02 .. 111 Y01 112 Y01 etc On Sheet1 cell B10 holds child code 111 Cell C10 has formula =VLOOKUP(B10,Sheet2!A1:B200,2,FALSE) It will return the Parent code best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "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 |
Lookup formula required
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 |
Lookup formula required
If Child Codes follow an alphabetical pattern this would be relatively easy,
is that the case. In other words if you sort the list of child codes would the be grouped such that parent codes would be together? Please also show us the layout of your data - Child Code Parent Code .... ? -- If this helps, please click the Yes button Cheers, Shane Devenshire "cangiff" wrote: 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 |
Lookup formula required
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 |
Lookup formula required
I am trying to create an easy reference function table for data that resides
within a networked General Ledger system. The child codes can be a combination of alpha & numeric. If I sorted the child codes, the parent codes would not run sequentially i.e. child code 111 belongs to parent code Y01 child code 100 belongs to parent code Y02 child code 121 belongs to parent code Y03 An extract of the way that I currently have my data set out is Parent Code Child code Child code Range start Range end Y01 111 120 Y01 AAA AAE Y02 100 110 Y02 131 141 Y02 ABB ABF Y03 121 130 Y03 141 150 Y03 AAF ABA Y03 Thanks for the help cangiff "Shane Devenshire" wrote: If Child Codes follow an alphabetical pattern this would be relatively easy, is that the case. In other words if you sort the list of child codes would the be grouped such that parent codes would be together? Please also show us the layout of your data - Child Code Parent Code ... ? -- If this helps, please click the Yes button Cheers, Shane Devenshire "cangiff" wrote: 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 |
Lookup formula required
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 |
Lookup formula required
Happy to help. Thanks for the feedback!
-- Biff Microsoft Excel MVP "cangiff" wrote in message ... 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 |
Lookup formula required
1.In sheet1 create two colomn as
A B Child Code Parent Code 2.In Sheet2 create the same columns with data as Child Code Parent Code 111 Y01 112 Y02 3.Now click cell B1 in sheet 1 4.Insert following formula =VLOOKUP(B2,Sheet3!A1:B6,2,TRUE) 5.Now drag the cells A and B down to apply the same formula to cells below. Have a happy time€¦. Chris ------ Convert your Excel spreadsheet into online calculator. http://www.spreadsheetconverter.com -- Chris ------ Convert your Excel spreadsheet into online calculator. http://www.spreadsheetconverter.com Message posted via http://www.officekb.com |
All times are GMT +1. The time now is 12:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com