Need help using Index/Match to return multiple qualifying values

[SammySpaceman]

Hi,

I have a list of titles in Column A and a corresponding number in Column HG. I'm trying to use Index/Match to return the titles with the highest numbers. My issue is that some of the titles have the same number. My formula returns the same title for both cells that have the same number, instead of the two or more titles that have that number.

Here's the formula I'm currently using: =INDEX('Full Stats'!$A$3:$A$98,MATCH(B78,'Full Stats'!$HG$3:$HG$98,0))

I've tried creating an Array formula to populate multiple results, but, each time, it only seems to populate the first cell.

This is the formula I've tried =IFERROR(INDEX('Full Stats'!$A$3:$A$98, SMALL(IF($B$78='Full Stats'!$HG$3:$HG$98, ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1), ROW(1:1))),"" )

I've tried holding Ctrl+Shift+Enter and dragging downwards, but it doesn't seem to do anything. Array formulas are not my area of expertise. Any help is greatly appreciated.

 

[KRice]

What version of Excel are you using?

 

[KRice]

Assuming B78 holds the quantity you intend to find matches for in column HG, I don't see any issues with the formula you've posted. Here I've worked up a small sample, except I placed the quantity to match in B8 rather than B78. For older versions of Excel (prior to 365), the formula would be entered as an array formula. Enter the formula normally, then go into the formula bar, hit F2 to enter edit mode, and then hit Ctrl-Shift-Enter simultaneously (I typically hold down Ctrl while then depressing Shift and finally Enter and then releasing all three). You should see curly brackets inserted around the formula in the formula bar. Then grab the "handle" in the lower right of the cell and drag that array formula down until no results are produced (those blanks are errors that are trapped by the IFERROR function).

Book1
	A	B	C																																																																																																																																																																																																																				HG
1
2	Titles		Results																																																																																																																																																																																																																				Qty
3	a		b																																																																																																																																																																																																																				3
4	b		d																																																																																																																																																																																																																				4
5	c																																																																																																																																																																																																																						2
6	d																																																																																																																																																																																																																						4
7	e	Qty to Match																																																																																																																																																																																																																					6
8	f	4																																																																																																																																																																																																																					7
9	g																																																																																																																																																																																																																						6
10
Full Stats

Cell Formulas
Range	Formula
C3:C6	C3	=IFERROR(INDEX('Full Stats'!$A$3:$A$98,SMALL(IF($B$8='Full Stats'!$HG$3:$HG$98,ROW('Full Stats'!$HG$3:$HG$98)-ROW('Full Stats'!$HG$3)+1),ROW(1:1))),"")

 

[SammySpaceman]

What version of Excel are you using?

2019

 

[SammySpaceman]

Thanks, KRice! I didn't realize I had to specifically grab the "handle". It works, now.

 

[KRice]

I’m happy to help.