Range of Values Returns One Result Rif 30.09.2006 06:08:01 Hello! I have a simple problem that probably requires a simple answer: I have a range of values in two columns and a third column that contains the value I want. The ranges NEVER overlap and are unique. For example: Row 1 (Columns A - C) have ICN000001, ICN000005, R45678 Row 2 (Columns A - C) have ICN000101, ICN000600, R01256 Row 3 (Columns A - C) have ICN001201, ICN003569, R85248 Row 4 (Columns A - C) have ICN006666, ICN006868, R45678 I can strip off the ICN value to ease the burden but notice how the ranges are not consecutive so I cannot sort and remove one of the columns as mentioned before. For example, I have (on Sheet2) a value in column A of 500. This would return R01256, as expected. But for a value such as 3999, I *do* want an N/A or something unique to signify there is no match. This way, I can weed out values that do not exist from a master consecutive list of values. **OR** (more complex) If there is a non VBA way to explicitly list a row of values from the range given in the first example. Using the table given above, I would like all values from all ranges (order is not important) since I could use a "range boundary" formula to populate the third, desired value I want. For example, I would have (after the formula): 1 2 3 4 5 101 102 .... 599 600 1201 1202 .... 3568 3569 ....and so on. ------ The first answer would help as I could manually do the second - though the second immediately would halep since some of the requests have a large amount of ranges. Any help would be appreciated!

RE: Range of Values Returns One Result Rif 30.09.2006 06:27:01 I should have been more clear. The ranges are not overlaping but can have a repeat for "start range" and "end range". So to clarify the request: Row 1 (Columns A - C) have ICN000001, ICN000005, R45678 Row 2 (Columns A - C) have ICN000009, ICN000009, R65489 << NEW Row 3 (Columns A - C) have ICN000101, ICN000600, R01256 Row 4 (Columns A - C) have ICN001201, ICN003569, R85248 Row 5 (Columns A - C) have ICN006666, ICN006868, R45678 Not sure if that would complicate things. Thanks! "Rif" wrote: [Quoted Text] > Hello! > > I have a simple problem that probably requires a simple answer: I have a > range of values in two columns and a third column that contains the value I > want. The ranges NEVER overlap and are unique. > > For example: > > Row 1 (Columns A - C) have ICN000001, ICN000005, R45678 > Row 2 (Columns A - C) have ICN000101, ICN000600, R01256 > Row 3 (Columns A - C) have ICN001201, ICN003569, R85248 > Row 4 (Columns A - C) have ICN006666, ICN006868, R45678 > > I can strip off the ICN value to ease the burden but notice how the ranges > are not consecutive so I cannot sort and remove one of the columns as > mentioned before. > > For example, I have (on Sheet2) a value in column A of 500. This would > return R01256, as expected. But for a value such as 3999, I *do* want an N/A > or something unique to signify there is no match. This way, I can weed out > values that do not exist from a master consecutive list of values. > > **OR** (more complex) > > If there is a non VBA way to explicitly list a row of values from the range > given in the first example. > > Using the table given above, I would like all values from all ranges (order > is not important) since I could use a "range boundary" formula to populate > the third, desired value I want. > > For example, I would have (after the formula): > > 1 > 2 > 3 > 4 > 5 > 101 > 102 > ... > 599 > 600 > 1201 > 1202 > ... > 3568 > 3569 > > ...and so on. > > ------ > > The first answer would help as I could manually do the second - though the > second immediately would halep since some of the requests have a large amount > of ranges. > > Any help would be appreciated!

Re: Range of Values Returns One Result "Roger Govier" <roger[ at ]technologyNOSPAM4u.co.uk> 30.09.2006 11:04:12 Hi Rif I created 2 helper columns D and E. In D1 =--MID(A1,4,6) copy across to E1, then copy D1:E1 down as far as required I put the value being looked up, 500 or 3999 in G1 then in H1 =IF(G1<=INDEX($E$1:$E$5,(MATCH(G1,$D$1:$D$5))), INDEX($C$1:$C$5,(MATCH(G1,$D$1:$D$5))),NA()) Change ranges to suit the size of your data, and copy the formula down. -- Regards Roger Govier "Rif" <Rif[ at ]discussions.microsoft.com> wrote in message news:373814E8-7AA2-4720-A2D2-A8FE72726400[ at ]microsoft.com... [Quoted Text] >I should have been more clear. The ranges are not overlaping but can >have a > repeat for "start range" and "end range". So to clarify the request: > > Row 1 (Columns A - C) have ICN000001, ICN000005, R45678 > Row 2 (Columns A - C) have ICN000009, ICN000009, R65489 << NEW > Row 3 (Columns A - C) have ICN000101, ICN000600, R01256 > Row 4 (Columns A - C) have ICN001201, ICN003569, R85248 > Row 5 (Columns A - C) have ICN006666, ICN006868, R45678 > > Not sure if that would complicate things. > > Thanks! > > > "Rif" wrote: > >> Hello! >> >> I have a simple problem that probably requires a simple answer: I >> have a >> range of values in two columns and a third column that contains the >> value I >> want. The ranges NEVER overlap and are unique. >> >> For example: >> >> Row 1 (Columns A - C) have ICN000001, ICN000005, R45678 >> Row 2 (Columns A - C) have ICN000101, ICN000600, R01256 >> Row 3 (Columns A - C) have ICN001201, ICN003569, R85248 >> Row 4 (Columns A - C) have ICN006666, ICN006868, R45678 >> >> I can strip off the ICN value to ease the burden but notice how the >> ranges >> are not consecutive so I cannot sort and remove one of the columns as >> mentioned before. >> >> For example, I have (on Sheet2) a value in column A of 500. This >> would >> return R01256, as expected. But for a value such as 3999, I *do* >> want an N/A >> or something unique to signify there is no match. This way, I can >> weed out >> values that do not exist from a master consecutive list of values. >> >> **OR** (more complex) >> >> If there is a non VBA way to explicitly list a row of values from the >> range >> given in the first example. >> >> Using the table given above, I would like all values from all ranges >> (order >> is not important) since I could use a "range boundary" formula to >> populate >> the third, desired value I want. >> >> For example, I would have (after the formula): >> >> 1 >> 2 >> 3 >> 4 >> 5 >> 101 >> 102 >> ... >> 599 >> 600 >> 1201 >> 1202 >> ... >> 3568 >> 3569 >> >> ...and so on. >> >> ------ >> >> The first answer would help as I could manually do the second - >> though the >> second immediately would halep since some of the requests have a >> large amount >> of ranges. >> >> Any help would be appreciated!