Need urgent help with Excel formula

[zeeshanbutt]

Hello All,

I want the following functionality in excel.

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

String to search:

OK
NOT OK
BLOCKING
NOT TESTE

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 th
formula need to be put in column E. Notice that Each row in column
should display the result from column A as soon as the string is foun
in column B. What formula do I need to put in column E & where? Pleas
suggest.

Thanks,

Ze

 

[plinius]

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.