Need urgent help with Excel formula

Hello All,

I want the following functionality in excel.

If in the entire column B if any of the following string is found get
the corresponding value of another cell in Column A.

String to search:

OK
NOT OK
BLOCKING
NOT TESTED

See the example at the following link:
http://www.flickr.com/photos/7714646@N07/7664399600/in/photostream

In this example the result found are displayed in Column E. So the
formula need to be put in column E. Notice that Each row in column E
should display the result from column A as soon as the string is found
in column B. What formula do I need to put in column E & where? Please
suggest.

Thanks,

Zee




--
zeeshanbutt

Il 29/07/2012 00:00, zeeshanbutt ha scritto:
> Hello All,
>
> I want the following functionality in excel.
>
> If in the entire column B if any of the following string is found get
> the corresponding value of another cell in Column A.
>
> String to search:
>
> OK
> NOT OK
> BLOCKING
> NOT TESTED
>
> See the example at the following link:
> http://www.flickr.com/photos/7714646@N07/7664399600/in/photostream
>
> In this example the result found are displayed in Column E. So the
> formula need to be put in column E. Notice that Each row in column E
> should display the result from column A as soon as the string is found
> in column B. What formula do I need to put in column E & where? Please
> suggest.
>
> Thanks,
>
> Zee
>
>
>
>


I2: OK
I3: NOT OK
I4: BLOCKING
I5: NOT TESTED

In E2 insert:
=INDIRECT("A"&SMALL(IF(ISNA(MATCH($B$2:$B$14,$I$2:$I$5,0)),"",ROW($B$2:$B$14)),ROW(A1)))
and copy down.

Hi,
E.