I have information exported from an accounting system into an Excel
spreadsheet. The export has 3 columns; the first column contains the value of
the first data of the range; the second column contains the end value of the
range; the third column shows a percentage rate applicable to all values
within the range. e.g.

C00123 C00125 15%
P00128 P00130 100%
T00523 T00528 15%

Note there is a gap between the end value at column 2 in row 1 and the next
value in column 1 of row 2. This is in effect an €œinvisible€� intervening
range of data that has a default percentage rate of 0%.

If I paste a separate random (i.e. not necessarily consecutive) list of
data, cut from a separate spreadsheet/source into a separate column, say, E,
such as:

C00124
C00125
C00127
P00130
P00131
T00520
T00525
T00533

is it possible to allocate the data in column 3 of the original export to
this list by a formula? i.e. I would like a formula copied down in column E
that would refer to the original export, and pick up the 3rd column data,
including the data not shown therein (i.e. the 0% allocated to the
"invisible" ranges between the ranges shown) to arrive at results showing:

C00124 15%
C00125 15%
C00127 0%
P00130 100%
P00131 0%
T00520 0%
T00525 15%
T00533 0%
et seq

Thank you for any help you can offer.

--
Geoff

=SUMPRODUCT(--(LEFT($A$1:$A$3,3)=LEFT(D1,3)),--(RIGHT($A$1:$A$3,3)+0<=RIGHT(D1,3)+0),--(RIGHT($B$1:$B$3,3)+0=RIGHT(D1,3)+0),$C$1:$C$3)


"Geoffric" wrote:

I have information exported from an accounting system into an Excel
spreadsheet. The export has 3 columns; the first column contains the value of
the first data of the range; the second column contains the end value of the
range; the third column shows a percentage rate applicable to all values
within the range. e.g.

C00123 C00125 15%
P00128 P00130 100%
T00523 T00528 15%

Note there is a gap between the end value at column 2 in row 1 and the next
value in column 1 of row 2. This is in effect an €œinvisible€� intervening
range of data that has a default percentage rate of 0%.

If I paste a separate random (i.e. not necessarily consecutive) list of
data, cut from a separate spreadsheet/source into a separate column, say, E,
such as:

C00124
C00125
C00127
P00130
P00131
T00520
T00525
T00533

is it possible to allocate the data in column 3 of the original export to
this list by a formula? i.e. I would like a formula copied down in column E
that would refer to the original export, and pick up the 3rd column data,
including the data not shown therein (i.e. the 0% allocated to the
"invisible" ranges between the ranges shown) to arrive at results showing:

C00124 15%
C00125 15%
C00127 0%
P00130 100%
P00131 0%
T00520 0%
T00525 15%
T00533 0%
et seq

Thank you for any help you can offer.

--
Geoff

Wow! Impressive. It does exactly what I wanted. And you only needed one
"bite" at the cherry! Thank you very much.
--
Geoff


"Teethless mama" wrote:

=SUMPRODUCT(--(LEFT($A$1:$A$3,3)=LEFT(D1,3)),--(RIGHT($A$1:$A$3,3)+0<=RIGHT(D1,3)+0),--(RIGHT($B$1:$B$3,3)+0=RIGHT(D1,3)+0),$C$1:$C$3)


"Geoffric" wrote:

I have information exported from an accounting system into an Excel
spreadsheet. The export has 3 columns; the first column contains the value of
the first data of the range; the second column contains the end value of the
range; the third column shows a percentage rate applicable to all values
within the range. e.g.

C00123 C00125 15%
P00128 P00130 100%
T00523 T00528 15%

Note there is a gap between the end value at column 2 in row 1 and the next
value in column 1 of row 2. This is in effect an €œinvisible€� intervening
range of data that has a default percentage rate of 0%.

If I paste a separate random (i.e. not necessarily consecutive) list of
data, cut from a separate spreadsheet/source into a separate column, say, E,
such as:

C00124
C00125
C00127
P00130
P00131
T00520
T00525
T00533

is it possible to allocate the data in column 3 of the original export to
this list by a formula? i.e. I would like a formula copied down in column E
that would refer to the original export, and pick up the 3rd column data,
including the data not shown therein (i.e. the 0% allocated to the
"invisible" ranges between the ranges shown) to arrive at results showing:

C00124 15%
C00125 15%
C00127 0%
P00130 100%
P00131 0%
T00520 0%
T00525 15%
T00533 0%
et seq

Thank you for any help you can offer.

--
Geoff

You're Welcome!

"Geoffric" wrote:

Wow! Impressive. It does exactly what I wanted. And you only needed one
"bite" at the cherry! Thank you very much.
--
Geoff


"Teethless mama" wrote:

=SUMPRODUCT(--(LEFT($A$1:$A$3,3)=LEFT(D1,3)),--(RIGHT($A$1:$A$3,3)+0<=RIGHT(D1,3)+0),--(RIGHT($B$1:$B$3,3)+0=RIGHT(D1,3)+0),$C$1:$C$3)


"Geoffric" wrote:

I have information exported from an accounting system into an Excel
spreadsheet. The export has 3 columns; the first column contains the value of
the first data of the range; the second column contains the end value of the
range; the third column shows a percentage rate applicable to all values
within the range. e.g.

C00123 C00125 15%
P00128 P00130 100%
T00523 T00528 15%

Note there is a gap between the end value at column 2 in row 1 and the next
value in column 1 of row 2. This is in effect an €œinvisible€� intervening
range of data that has a default percentage rate of 0%.

If I paste a separate random (i.e. not necessarily consecutive) list of
data, cut from a separate spreadsheet/source into a separate column, say, E,
such as:

C00124
C00125
C00127
P00130
P00131
T00520
T00525
T00533

is it possible to allocate the data in column 3 of the original export to
this list by a formula? i.e. I would like a formula copied down in column E
that would refer to the original export, and pick up the 3rd column data,
including the data not shown therein (i.e. the 0% allocated to the
"invisible" ranges between the ranges shown) to arrive at results showing:

C00124 15%
C00125 15%
C00127 0%
P00130 100%
P00131 0%
T00520 0%
T00525 15%
T00533 0%
et seq

Thank you for any help you can offer.

--
Geoff