reverse lookup

[lmcd5666]

Hi all,

Hope someone can help.
Situation:
.....A.....B......C......D
1....0.....1......400....0
2....0.....2......300....0
3....0.....3......350....0
4....0.....4......150...150
5....2.....0.......0.....0
6....3.....0.......0.....0
7....1.....0.......0.....0

I need a formula that make values in column D match the value in column C (same row) but only if the value in column B(same row)
is NOT find in column A range. Values in column A are not in any order. Values in column B are in ascending order. If there is a match then the value in column is zero.

As you can see from the above example..D4 equals c4 because there was no matching of B4 in the column A range. The first three row...there are matches, so the values in the D's cells are zero.

Any help would be greatly appreciated.

Toan

 

[Asala42]

Situation:
.....A.....B......C......D
1....0.....1......400....0
2....0.....2......300....0
3....0.....3......350....0
4....0.....4......150...150
5....2.....0.......0.....0
6....3.....0.......0.....0
7....1.....0.......0.....0

I need a formula that make values in column D match the value in column C (same row) but only if the value in column B(same row)
is NOT find in column A range. Values in column A are not in any order. Values in column B are in ascending order. If there is a match then the value in column is zero.

As you can see from the above example..D4 equals c4 because there was no matching of B4 in the column A range. The first three row...there are matches, so the values in the D's cells are zero.

Try:
=IF(Countif($A$2:$A$10,B2)>0,0,D2)

 

[lmcd5666]

Thank you, Asala42. It worked out great.