Sounds simple but isn't - displaying cell text

[pieman3]

Hi

I am trying to create a list of products in one column that are rated either
good or excellent in another column on the same sheet. So I need the list to
display in one column based upon the values of two other separate columns.

For example, I have 3 columns in total, column A is where I want the product
names to be displayed, column B is the text I want to display in column A
only if the corresponding cell in column C contains the words 'excellent' or
'good'. So if cell B1 contains the text 'widget 1' and the text in cell C1
contains the text 'excellent' or 'good' then 'widget 1' should be shown in
cell A1. Ideally i want the list to only contain the qualifying products
without empty cells caused by products that don't meet the criteria.

Is there a formula to create this? Any ideas would be much appreciated as
I've spent hours trying to find the solution.

Thanks
Simon

 

[nelly]

Hi

This should do it, and is not case sensitive

=IF(OR(C1="excellent",C1="good"),B1,"")

Regards
Nelly

 

[T. Valko]

Try this...

Let's assume your data is in the range B1:C20

Create these defined names
Goto the menu Insert>Name Define

Name: Item
Refers to: $B$1:$B$20

Name: Status
Refers to: $C$1:$C$20

Name: Rating
Refers to: ={"good","excellent"}

Enter this array formula** in A1:

=IF(ROWS(A$1:A1)<=SUM(COUNTIF(Status,Rating)),INDEX(Item,SMALL(IF(Status=Rating,ROW(Item)),ROWS(A$1:A1))-MIN(ROW(Item))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get blanks.

 

[Herbert Seidenberg]

Excel 2007
Three more methods:
Pivot Table without formulas.
Advanced Filter without formulas.
Advanced Filter with macro.
http://www.mediafire.com/file/iwwj2nmjyjw/04_04_09.xlsm

 

[pieman3]

Thanks Nelly, I tried this and it did copy the text like you said. The only
problem was that it copied blanks from cells that didn't meet the rating
criteria. I can use this elsewhere in my sheet though so many thanks for your
advice

 

[pieman3]

Biff, thanks so much for your excellent post. Your forumla works great and
I've learned something new as well, didn't know about 'names'.

Thanks again for your help, much appreciated

 

[T. Valko]

You're welcome. Thanks for the feedback!