Forum Discussion

KJSmithy's avatar
KJSmithy
Copper Contributor
Oct 22, 2022

INDEX Multiple Match

Hi all, I am having a bit of a struggle finding the right way to exclude an Index hit already found. Due to the nature of the work I am doing, I an unable to share the exact file. The code to look...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 22, 2022

KJSmithy 

=INDEX(filepath!C:C,SMALL(IF((B11=filepath!W:W)*(D11=filepath!A:A),ROW(filepath!C:C)),ROW(C11)-10))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

Worksheet FILEPATH

 If you work with Office365 or 2021 you can apply FILTER function.

KJSmithy's avatar
KJSmithy
Copper Contributor
Oct 23, 2022
Hi all,
Thank you for this, however I seem to not receive back the multiple hits when using

=INDEX('[FILEPATH]GBC6 Report'!C:C,SMALL(IF((B11='[FILEPATH]GBC6 Report'!W:W)*(D11='[FILEPATH]GBC6 Report'!A:A),ROW('[FILEPATH]GBC6 Report'!C:C)),ROW(C11)-10))

It only provides me with the first match.

I wish I could upload screenshots, but I don not have permission to upload pics.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 23, 2022

    KJSmithy 

    =IFERROR(INDEX('FILEPATH GBC6 Report'!C:C,SMALL(IF(($B$11='FILEPATH GBC6 Report'!W:W)*($D$11='FILEPATH GBC6 Report'!A:A),ROW('FILEPATH GBC6 Report'!C:C)),ROW(C11)-10)),"")

     

    Attached is my file. Maybe this helps to find a solution in your file. As worksheet name i have to use "FILEPATH GBC6 Report" because square brackets [ ] are not allowed inside a worksheet name in my file.

     

    I've entered the formula in cell C11 and confirmed with ctrl+shift+enter. Then i dragged to formula across range C11:C15.

     

Resources