Excel to return value if multiple criteria met

[emortals]

Hi Gang,

Trying to find a formula to return a value of column D:D if multiple criteria has been met. The thing is the same criteria might be there many times so there needs to be some sort of loop to keep returning value in D:D each time the criteria is met

Example:

A	B	C	D
Cuisine	Dish	Rating	Feedback
Chinese	Noodles	1	the food sucked
Chinese	pecking duck	1	it was cold
Chinese	soup	1	not enough seasoning

Criteria: If Cuisine is Chinese and the Dish is Noodles and the Rating is a 1 then return the feedback

I have a spreadsheet with hundreds of these entries and I want to be able to extract the negative feedback based on the criteria above. Note, any dish with a rating between 1 and 6 is considered negative.

Is there one formula I can run that will give me all the negative responses?

[vamosj]

This one may need a little more information on how the output is displayed. Are you returning the values on a column on the same sheet or being returned on on another sheet? Better yet, upload an example spreadsheet.

[emortals]

Thanks for your help.

See attached Sample: sample.xlsx

I would like to list all the negative feedback on one sheet (sheet1). I have another sheet with all the raw data (data).

Thanks!

[Tony Valko]

Try this...

Data Range

	A	B	C
1	Cuisine	Chinese
2	Dish	Rating 1-6	Feedback
3	Noodles	1	the food sucked
4	pecking duck	3	it was cold
5	soup	5	not enough seasoning
6	lamian	6	portion size was too small
7

Enter this array formula** in A3:

=IFERROR(INDEX(data!B:B,SMALL(IF(data!$A$2:$A$13=$B$1,IF(data!$C$2:$C$13<=6,ROW(data!$A$2:$A$13))),ROWS(A$3:A3))),"")

** 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 across to C3 then down until you get blanks.

[emortals]

Hi Biff,

Your method works but I just realised the raw data actually has some columns in between so when I apply your formula it will give me data from the 3 rows next that are next to each other. If I told you which columns the data lives for "Cuisine", "Dish", "Rating" and "Feedback" will you be able to work that in?

Cuisine = Column C
Dish = Column D
Rating = Column H
Feedback = Column I

Thanks in advance!

[Tony Valko]

OK, in that case, instead of using one formula and copying it, you'll need three formulas, one for each column.

ALL formulas are array formulas**.

Entered in A3:

=IFERROR(INDEX(data!D:D,SMALL(IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13))),ROWS(A$3:A3))),"")

Entered in B3:

=IFERROR(INDEX(data!H:H,SMALL(IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13))),ROWS(B$3:B3))),"")

Entered in C3:

=IFERROR(INDEX(data!I:I,SMALL(IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13))),ROWS(C$3:C3))),"")

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

Select A3:C3 and copy down until you get blanks.

[emortals]

works perfectly.. lastly, I am trying to add one more criteria but cant seem to get it to work. I want to add it to all three arrays: IF(data!$D$2:$D$300=$B$2

Also, i noticed when there is no feedback it outputs the number 0.. can we have it output as blank instead?

[Tony Valko]

Like this...

Entered in A3:

=IFERROR(INDEX(data!D:D,SMALL(IF(data!$D$2:$D$13=$B$2,IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13)))),ROWS(A$3:A3))),"")

Entered in B3:

=IFERROR(INDEX(data!H:H,SMALL(IF(data!$D$2:$D$13=$B$2,IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13)))),ROWS(B$3:B3))),"")

Entered in C3:

=IFERROR(INDEX(data!I:I,SMALL(IF(data!$D$2:$D$13=$B$2,IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,IF(data!$I$2:$I$13<>"",ROW(data!$C$2:$C$13))))),ROWS(C$3:C3))),"")

Don't forget: Array entered!

[emortals]

Like this...

Entered in A3:

=IFERROR(INDEX(data!D:D,SMALL(IF(data!$D$2:$D$13=$B$2,IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13)))),ROWS(A$3:A3))),"")

Entered in B3:

=IFERROR(INDEX(data!H:H,SMALL(IF(data!$D$2:$D$13=$B$2,IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,ROW(data!$C$2:$C$13)))),ROWS(B$3:B3))),"")

Entered in C3:

=IFERROR(INDEX(data!I:I,SMALL(IF(data!$D$2:$D$13=$B$2,IF(data!$C$2:$C$13=$B$1,IF(data!$H$2:$H$13<=6,IF(data!$I$2:$I$13<>"",ROW(data!$C$2:$C$13))))),ROWS(C$3:C3))),"")

Don't forget: Array entered!

Hi Tony, for the formula specified for C3, that will exclude all results with blank feedback. I don't want to exclude them I just want them to be returned as blank instead of zero values.

[Tony Valko]

Try this...

Still array entered.

=IFERROR(INDEX(Data!I$2:I$13&"",SMALL(IF(Data!D$2:D$13=B$2,IF(Data!C$2:C$13=B$1,IF(Data!H$2:H$13<=6,ROW(Data!I$2:I$13)))),ROWS(C$3:C3))-ROW(I$2)+1),"")