INDEX and MATCH function with finding two rows and display their content

[nsa1]

Hello,

I have an issue where I would like to add contents of multiple rows of a referenced column via INDEX and MATCH function into one cell...

I've added snapshots below.

I would want a cell under Andy where the formula is to return "xyx, yz". My current formula only returns the first value of the row it finds.

thanks,

NA

 

[AhoyNC]

With your version of Excel I don't think you will be able to use a formula to put the answer in one cell. This formula will find both or more answers and put them in columns of the same row.
Enter the formula in H3 and drag across columns.

Book1
	A	B	C	D	E	F	G	H	I
1
2		Name	Jan	Feb	Mar		Name	Andy
3	1	Alper	$11,882	$11,519	$7,565			xyx	YZ
4	2	Burrows	$11,676	$6,344	$5,406
5	3	Chandler	$10,296	$9,693	$11,867
6	4	Colby	$4,752	$6,786	xyz
7	5	Frantz	$10,699	$5,194	$10,525
8	6	Gonzalez	$10,404	xyz	$8,964
9	7	Kyle	$11,841	$4,689	$10,992
10	8	Little	$5,259	$3,900	$7,845
11	9	Long	$6,364		$4,759
12	10	Andy	$999	xyx	$99
13	11	Andy	xyx	YZ	$100
Sheet1

Cell Formulas
Range	Formula
H3:I3	H3	=IFERROR(INDEX($C$3:$E$13,AGGREGATE(15,6,(ROW($B$3:$B$13)-ROW($B$3)+1)/($B$3:$B$13=$H$2),COLUMNS($H$3:H$3)),2),"")

 

[PeteWright]

Hi @nsa1, few questions:
1. Could you confirm your Excel version please, is it only Excel 2010?
2. Would you accept a custom VBA function?
3. What is the exact criteria for the result, eg. only text or everything except a number?