Help with a 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 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 promptly suggest.

Thanks,
Zee

 

[Aladin Akyurek]

Thanks Ron. It worked

So control+shift+enter did not work you?

 

[zeeshanbutt]

Sorry it did not. I am a basic excel user. I tried it but it did not work for me.

 

[Aladin Akyurek]

Sorry it did not. I am a basic excel user. I tried it but it did not work for me.

It should have been... Control+shift+enter means: Press down the control and shift keys while you hit the enter key.

[TABLE="width: 324"]
<colgroup><col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3043;" width="86"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2872;" width="81"> <col style="width: 48pt;" width="64"> <col style="width: 69pt; mso-width-source: userset; mso-width-alt: 3271;" width="92"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;" width="109"> <tbody>[TR]
[TD="class: xl65, width: 86, bgcolor: transparent"]Reference no[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent"]String[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 92, bgcolor: transparent"]Search Strings[/TD]
[TD="class: xl65, width: 109, bgcolor: transparent"]Reference found[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl66, bgcolor: transparent"]OK[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]OK[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NOT OK[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl66, bgcolor: transparent"]NOT OK[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]BLOCKING[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl66, bgcolor: transparent"]OK[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NOT TESTED[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, bgcolor: transparent"]OK[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, bgcolor: transparent"]BLOCKING[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl66, bgcolor: transparent"]NOT TESTED[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

D2:D5 houses the search strings...

E2, control+shift+enter and copy down:

=IFERROR(INDEX($A$2:$A$14,SMALL(IF(ISNUMBER(MATCH($B$2:$B$14,$D$2:$D$5,0)),
  ROW($B$2:$B$14)-ROW($B$2)+1),ROWS($E$2:E2))),"")

One would miss an important class of formulas (so-called array formulas) if control+shift+enter (CSE) would not work.