Lookup not working

Hi

**I have attached an example xls as the formatting of my below example is not clear**

The following formula is copied down column E but it isn't working as I want it to.

=LOOKUP(2,1/($A$2:$A$5=D2)*($B$2:$B$5<>"Pending")*($B$2:$B$5<>"New")*($B$2:$B$5<>""),$C$2:$C$5)

What I want it to do in say, e2, is this:

Where a2:a5 equals d2 and b2:b5 does not equal Pending or New or "", return the value in c2:c5.

A B C D E

1 Pending a 1 a
2 New b 2 b
3 c 3 c
4 August d 4 d

Thanks

penfold,

In your example, the formula woudn't return anything because the D2 = "b", but there is no "b" in column A.