Range of Values Returns One Result
 

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!

Range of Values Returns One Result
 

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!

Range of Values Returns One Result
 

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" wrote in message
...
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!