Need help using Index/Match to return multiple qualifying values

SammySpaceman

Board Regular
Joined
Aug 18, 2002
Messages
64
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
ABCHG
1
2TitlesResultsQty
3ab3
4bd4
5c 2
6d 4
7eQty to Match6
8f47
9g6
10
Full Stats
Cell Formulas
RangeFormula
C3:C6C3=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))),"")
 
Last edited:
Upvote 0
Solution
Thanks, KRice! I didn't realize I had to specifically grab the "handle". It works, now.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top