Forum Discussion
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 up the info is below
=IFERROR(INDEX(‘FILEPATH'!$C:$C,MATCH(1,(B11='FILEPATH'!$W:$W)*(D11='FILEPATH’!$A:$A),0)),)
It matches everything correctly, however if there are two hits I only receive the initial hit.
Any help would be much appreciated.
6 Replies
=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.
- 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.=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.
Hi! Unfortunately, I have to tell you that MATCH(), just like XLOOKUP(), VLOOKUP() or XMATCH(), always produces only the first hit. The good news is that with FILTER() from Excel 2019 we have a function that can do this.
Your formula should read:=FILTER(C:C,(B11=FILEPATH!$W:$W)*(D11=FILEPATH!$A:$A),"")
