Formula help.

[Sondrelarsen]

I need help with this formula.
=IFERROR(INDEX(Spillerinfo!$D$2:$D$29998;MATCH(A40;Spillerinfo!$C$2:$C$29998;0))&" ("&INDEX(Spillerinfo!$F$2:$F$29998;MATCH(A40;Spillerinfo!$C$2:$C$29998;0))&")";"")

So what i am doing with this formula is look up players and their position on the field in the last part of the formula.
it brings up every player from each team and when there is no more players it shows ''( )'' in the cells, can anyone help me to get it to show blank if it cant find anymore players.

thanks

 

[jasonb75]

The easiest way would be to use SUBSTITUTE

=SUBSTITUTE(IFERROR(INDEX(Spillerinfo!$D$2:$D$29998;MATCH(A40;Spillerinfo!$C$2:$C$29998;0))&" ("&INDEX(Spillerinfo!$F$2:$F$29998;MATCH(A40;Spillerinfo!$C$2:$C$29998;0))&")";"");" ()";"")

Note that your formula appears as though it should be returning " ()" rather than the "( )" that you have entered in the second paragraph of your question.
If your formula is returning a space between the parentheses then you would need to add one into the formula in order for it to work.

 

[KRice]

Other than the "( )" issue, does the formula perform the way you want it to? The formula will return a singular result for the first match found where A40=Spillerinfo!$C$2:$C$29998. Is that what you want? Your description sounds as if you expect the formula to return multiple results for anywhere that A40=Spillerinfo!$C$2:$C$29998, even multiple matches.

 

[Sondrelarsen]

Other than the "( )" issue, does the formula perform the way you want it to? The formula will return a singular result for the first match found where A40=Spillerinfo!$C$2:$C$29998. Is that what you want? Your description sounds as if you expect the formula to return multiple results for anywhere that A40=Spillerinfo!$C$2:$C$29998, even multiple matches.

yes, i have multiple formulas, so the formula in it self works just fine. its just when i have got every player from each team the ''( )'' shows up. just like this. i just want it to show blank when i have every player

 

[Sondrelarsen]

The easiest way would be to use SUBSTITUTE

=SUBSTITUTE(IFERROR(INDEX(Spillerinfo!$D$2:$D$29998;MATCH(A40;Spillerinfo!$C$2:$C$29998;0))&" ("&INDEX(Spillerinfo!$F$2:$F$29998;MATCH(A40;Spillerinfo!$C$2:$C$29998;0))&")";"");" ()";"")

Note that your formula appears as though it should be returning " ()" rather than the "( )" that you have entered in the second paragraph of your question.
If your formula is returning a space between the parentheses then you would need to add one into the formula in order for it to work.

Thanks alot mate, very helpful