Forum Discussion
KJSmithy
Oct 22, 2022Copper Contributor
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...
OliverScheurich
Oct 22, 2022Gold Contributor
=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.
- KJSmithyOct 23, 2022Copper ContributorHi 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.- OliverScheurichOct 23, 2022Gold Contributor
=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.
- KJSmithyOct 23, 2022Copper Contributor
Perhaps this can help a bit 😃