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

 

[madforgolf]

Hi

Place this in E1 and copy down.

=IF(B1="ok",A1,IF(B1="Not ok",A1,IF(B1="blocking",A1,IF(B1="not tested",A1,""))))

 

[zeeshanbutt]

Thanks madforgolf.

But in my result (Column E) I dont want any gaps in the rows. See how my result is displayed in the photo that I provided. Can you please help with that?

 

[Aladin Akyurek]

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

Enter the strings to search in D2:D5.

E2, control+shift+enter, not just 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))),"")

 

[Ron Coderre]

With your posted example
and
H1:H4 containing this list:
OK
NOT OK
BLOCKING
NOT TESTED

This regular formula begins the list of Col_A values where the corresponding Col_B item matches an item in the H1:H4 list:

E2: =INDEX($A:$A,SMALL(INDEX(ISERROR(MATCH($B$2:$B$20,$H$1:$H$4,0))*99^99+ROW($B$2:$B$20),0),ROWS($2:2)))

Copy that formula down through E7

Is that something you can work with?

 

[zeeshanbutt]

@ Aladin Akyurek,

control+shift+enter does not seem to do anything for me. I put the search string in D2:D5 and paste your formula in column E but I am only getting one rcord of 1. This does not seem right? Am I doing something wrong? Please suggest.

 

[zeeshanbutt]

@ Ron Coderre,

Your formula works but I cannot limit it to row 7 since I may have data until the very last of row of excel, and thus for rows that does not have any data the cells says "#REF". Can you please fix your formula so that the result look like the one in my example (excel photo)? Thanks a lot.

 

[Aladin Akyurek]

@ Aladin Akyurek,

control+shift+enter does not seem to do anything for me. I put the search string in D2:D5 and paste your formula in column E but I am only getting one rcord of 1. This does not seem right? Am I doing something wrong? Please suggest.

Control+shift+enter means: Press down the control and shift keys at the same time while you hit the enter key.

 

[Ron Coderre]

If you have Excel 2007 or later, you could use this regular formula:

E2: =IFERROR(INDEX($A:$A,SMALL(INDEX(ISERROR(MATCH(B:B,$H$1:$H$4,0))*99^99+ROW(B:B),0),ROWS($2:2))),"")

Copy that formula down.

However, that might make the workbook a bit sluggish. You'd be better to convert your data range to an Excel table (so it will automatically resize to match your data area). If you need help with that, let us know.

Does that help?

 

[zeeshanbutt]

Thanks Ron. It worked