=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
|
Similar Threads
