Display blank if not found & if multiple found show in same cell

[jayphilips]

Issue 1: Index finds multiple values but only displays one. I'd like it to combine the results in the cell
Issue 2: If the match is not found the result is #N/A. I'd like it to be blank
Data:
Year Domain SubDomain Application Release Unit Test Defects Rank Integration Defects Rank
2007 Domain1 Sub1 App1 Aug-07 28 1 0 26
2007 Domain1 Sub1 App2 Jun-07 15 2 1 17
2007 Domain1 Sub1 App3 Nov-07 11 3 0 26
2007 Domain1 Sub1 App4 Aug-07 6 4 0 26
2007 Domain1 Sub1 App5 Aug-07 3 5 0 26
2007 Domain1 Sub1 App6 Aug-07 2 6 0 26
2007 Domain1 Sub1 App1 Nov-07 1 7 10 8
2007 Domain1 Sub1 App9 May-07 1 7 16 4
2007 Domain1 Sub1 App10 Nov-07 1 7 0 26
2007 Domain1 Sub1 App11 Aug-07 1 7 0 26
2007 Domain1 Sub1 App12 Aug-07 1 7 2 14
2007 Domain1 Sub1 App13 Aug-07 1 7 1 17
2007 Domain1 Sub1 App14 Aug-07 1 7 1 17
2007 Domain1 Sub1 App1 Dec-07 0 14 0 26
2007 Domain1 Sub1 App2 Jun-07 0 14 0 26
2007 Domain1 Sub1 App3 May-07 0 14 3 11
2007 Domain1 Sub1 App4 Nov-07 0 14 0 26
2007 Domain1 Sub1 App5 Aug-07 0 14 0 26

Results from forumlas
Match Rank Application Release Unit Test
1 1 App1 Aug-07 28
2 2 App2 Jun-07 15
3 3 App3 Nov-07 11
4 4 App4 Aug-07 6
5 5 App5 Aug-07 3
6 6 App6 Aug-07 2
7 7 App1 Nov-07 1
#N/A 8 #N/A #N/A #N/A
#N/A 9 #N/A #N/A #N/A
#N/A 10 #N/A #N/A #N/A

Forumlas:
Match Rank Application Release Unit Test
=MATCH(E6,$I$19:$I$300,0) 1 =INDEX($F$19:$F$300,$D6) =INDEX($G$19:$G$300,$D6) =INDEX($H$19:$H$300,$D6)
=MATCH(E7,$I$19:$I$300,0) 2 =INDEX($F$19:$F$300,$D7) =INDEX($G$19:$G$300,$D7) =INDEX($H$19:$H$300,$D7)
=MATCH(E8,$I$19:$I$300,0) 3 =INDEX($F$19:$F$300,$D8) =INDEX($G$19:$G$300,$D8) =INDEX($H$19:$H$300,$D8)
=MATCH(E9,$I$19:$I$300,0) 4 =INDEX($F$19:$F$300,$D9) =INDEX($G$19:$G$300,$D9) =INDEX($H$19:$H$300,$D9)
=MATCH(E10,$I$19:$I$300,0) 5 =INDEX($F$19:$F$300,$D10) =INDEX($G$19:$G$300,$D10) =INDEX($H$19:$H$300,$D10)
=MATCH(E11,$I$19:$I$300,0) 6 =INDEX($F$19:$F$300,$D11) =INDEX($G$19:$G$300,$D11) =INDEX($H$19:$H$300,$D11)
=MATCH(E12,$I$19:$I$300,0) 7 =INDEX($F$19:$F$300,$D12) =INDEX($G$19:$G$300,$D12) =INDEX($H$19:$H$300,$D12)
=MATCH(E13,$I$19:$I$300,0) 8 =INDEX($F$19:$F$300,$D13) =INDEX($G$19:$G$300,$D13) =INDEX($H$19:$H$300,$D13)
=MATCH(E14,$I$19:$I$300,0) 9 =INDEX($F$19:$F$300,$D14) =INDEX($G$19:$G$300,$D14) =INDEX($H$19:$H$300,$D14)
=MATCH(E15,$I$19:$I$300,0) 10 =INDEX($F$19:$F$300,$D15) =INDEX($G$19:$G$300,$D15) =INDEX($H$19:$H$300,$D15)

Any ideas on how to fix these?

Thanks in advance,
Jay

 

[phxsportz]

Re: Displau blank if not found & if multiple found show in same cell

=if(isna(match(e6,$i$19:$i$300,0)),"",match(e6,$i$19:$i$300,0))

 

[jayphilips]

Re: Displau blank if not found & if multiple found show in same cell

Thank you that formula worked great for not dispalying the #N/A.

Now I still have the issue on if a result shows up more than once. Is it even possible to get multiple results to show up in the same field?

My Index formula is now:
=IF($D6="", "", INDEX($F$19:$F$300,$D6))

Thank you,
Jay