I believe I understand better now. Try this formula instead of the other one
and let me know if this does what you need it to do. As before, this formula
would go into a cell on Row 2 and you need to change the range references to
match your data.

=IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",IF(C20,"Offsetting
-ve is at: " &
IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)) & " for " & B2,""))

"Radhakant Panigrahi" wrote:

Hello JLatham,

i tried the both the formullae, but did not worked...if i look into the data
i need to find the below IMP ID in column "E" where i have written "Offset"
where as your formula giving comment against the -ve value... i want to
identify the +value of IMP ID that is offsetting the -ve value of the same
EMP ID

Manager IDEMP ID No of days '-ve value' Comments Comments
454 12121 5 No Offset
454 12121 15 No
454 12121 -5 Yes Matching +ve is at: $B$2
454 12121 4 No Offset
454 12121 -4 Yes Matching +ve is at: $B$5
454 12121 10 No
878 45464 14 No
878 45464 -2 Yes Matching +ve is at: $B$10
878 45464 2 No Offset

Regards,
rkp

"JLatham" wrote:

To work properly when there is no matching +ve entry in the list, use this
formula instead:

=IF(C20,"Matching +ve is at: " &
IF(ISERR(ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"no
match
yet",ADDRESS(SUMPRODUCT(--(B$2:B$10=B2),--(C$2:C$10=-C2),(ROW(B$2:B$10))),2)),"")

Once more, adjust the ranges involved to match the length of your data
columns.


"Radhakant Panigrahi" wrote:

Hi,

I have below data with me, i have 4 columns where the "no of days" for
Employee ID (under a particular manager ID) is coming with -ve value as well
as with +ve value.

The "no of days" are automatically coming whether it is -ve or +ve comes in
the report in column "D".

I want a formula in column "E" where it will find for me the Emp ID having
+ve value that is offsetting with the same Emp ID having the -ve value

Manager Empl ID no of days -ve value comment
ID
454 12121 5 No
454 12121 15 No
454 12121 -5 Yes
454 12121 4 No
454 12121 -4 Yes
454 12121 10 No
878 45464 14 No
878 45464 -2 Yes
878 45464 2 No


Regards,
rkp