hi,
i am having an issue using the index and match formula.
what i need is to return a value from column 2 when the match value ="Breach", there is generally 10-15 matches of this value in 500 rows of data.
the issue is that when i place this formula
=INDEX('SPA Report'!A1:O500,(MATCH("BREACH",'SPA Report'!K1:K500,0)),2)
and drag it down it will only return the first instance of "breach" in the table array.
is there a way that i can make the formula return the next instance of breach in the arrayeach time??
could i use an IF formula??
anyhelp would be appreciated.
thanks
i am having an issue using the index and match formula.
what i need is to return a value from column 2 when the match value ="Breach", there is generally 10-15 matches of this value in 500 rows of data.
the issue is that when i place this formula
=INDEX('SPA Report'!A1:O500,(MATCH("BREACH",'SPA Report'!K1:K500,0)),2)
and drag it down it will only return the first instance of "breach" in the table array.
is there a way that i can make the formula return the next instance of breach in the arrayeach time??
could i use an IF formula??
anyhelp would be appreciated.
thanks