Sounds simple but isn't - displaying cell text

P

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
 
N

nelly

Hi

This should do it, and is not case sensitive

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

Regards
Nelly
 
T

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.
 
P

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 :)
 
P

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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top