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