INDEX MATCH the second result

[j4ymf]

Hello

I have a formula that I'm using but i would like to return the second and third result
=INDEX(Data!A:A,MATCH('HSEQ REPORT'!$A$1,Data!A:A),0)


thank you

 

[jasonb75]

Assuming that in reality the INDEX range and MATCH range will be different columns.

=FILTER(Data!$A$2:$A$100,Data!$A$2:$A$100='HSEQ REPORT'!$A$1,"No Match")

You can not return multiple matches without an array formula. Using full columns with arrays is not advisable, they become very inefficient very quickly.

If the INDEX and MATCH range are the same (as with your example) then I would simply use COUNTIF to check the number of matches in the range then use IF and ROWS to copy the criteria cell without matching each time.
With the formula in A2,

=IF(ROWS($A$2:$A2)>COUNTIF(Data!A:A,'HSEQ REPORT'!$A$1),"",'HSEQ REPORT'!$A$1)