Return Multiple Results for a Single Match

[MibuKotarou]

Hello MrExcel forum,

I am trying to figure out a formula I can use to return a series of cells for only a single match query. I have a series of yes or no questions that have a value attached to them. I want to search the array for instances of "Yes" and then return the values two columns over but the catch is I need the values all in the same cell.

Yes	Exist?	Apple
Yes	Exist?	Banana
	Exist?	Ghost
Yes	Exist?	Horse
	Exist?	Vampire

<tbody>
</tbody>

I would like it to return Apple. Banana. Horse.

Help me MrExcel Forum You are My Only Hope.

Thank You

 

[engineer69]

You may be able to define a range object and then use a "For each "Yes" in range" loop and return the cooresponding item in the third column. You would then need a string concatenation something like theText = theText & [column 3 data] & " " which must be inside the loop.

 

[Matty]

Hi,

Based on a layout as follows:

Excel 2012

	A	B	C	D	E
1	Question	Present?	Thing		Result
2	Yes	Exist?	Apple		Apple
3	Yes	Exist?	Banana		Banana
4		Exist?	Ghost		Horse
5	Yes	Exist?	Horse
6		Exist?	Vampire

<tbody>
</tbody>

Sheet1​

E2, copied down:

=IFERROR(INDEX(C$2:C$6,SMALL(IF(A$2:A$6="Yes",ROW(C$2:C$6)-ROW(C$2)+1),ROWS(E$2:E2))),"")

Array entered, i.e. with CTRL+SHIFT+ENTER, before dragging down.

Matty

 

[MibuKotarou]

Hi,

Thanks for your help. I am having an issue with the IF statement. It is saying that A$2:A$6="Yes" is not valid and causing an error in the formula which then only shows the First result in E2 and nothing in the later fields.