CONCATENATE four cells defined by INDEX MATCH

I'm trying to add data on who is related to whom to a spreadsheet. I dumped every bit of data on relationships for everyone in my database into a new tab called "Relations" and then set up an INDEX MATCH. My formula (below) works perfectly.

=INDEX(Relations!G$2:G$3000,MATCH($A2),Relations!$E$2:$E$3000,0))

BUT then I realized that when there are multiple relatives they are listed in separate columns, so I need data from columns G, H, I and J. I can CONCATENATE the INDEX MATCH four times (once fro each target column) as below, but it results in extra commas all over the place because there is not necessarily data in all (or any) of the target columns.

=CONCATENATE(INDEX(Relations!G$2:G$3000,MATCH($A2),Relations!$E$2:$E$3000,0)),", ",INDEX(Relations!H$2:H$3000,MATCH($A2),Relations!$E$2:$E$3000,0)),", ",INDEX(Relations!I$2:I$3000,MATCH($A2),Relations!$E$2:$E$3000,0)),", ",INDEX(Relations!J$2:J$3000,MATCH($A2),Relations!$E$2:$E$3000,0)))

Does anyone know of a better way to do this? My internet searches didn't come up with anything, so I'm turning to the forum. Any ideas are appreciated.

First of all, you don't really need to use the CONCATENATE( .. ) function - you can use the concatenation operator (&) instead.

Secondly, you seem to have a closed bracket immediately after the $A2 in your MATCH function (and in the longer formula), which should not be there.

You can avoid those multiple commas by doing it like this:

=SUBSTITUTE(G2&", " & IF(H2="","",H2&", ") & IF(I2="","",I2&", ") & IF(J2="","",J2&", ") & "$$",", $$","")

Obviously, I've simplified your cell references, but hopefully you will be able to relate it to your own situation. I've also assumed that there will be a value from column G, so no need for an IF on that column. Basically the comma space gets added automatically to any non-blank cells, so eventually the final entry has a comma space at the end of the string. So, add on some special character, and use SUBSTITUTE to get rid of that and the redundant comma space.

Hope this helps.

Pete

Thanks Pete, but how do I make that work with the INDEX MATCH to pull the data from the right line?

Instead of G2 in my formula, you would have the expression that involves column G, i.e. change it to:

INDEX(Relations!G$2:G$3000,MATCH($A2,Relations!$E$2:$E$3000,0))

Similarly, change H2 to:

INDEX(Relations!H$2:H$3000,MATCH($A2,Relations!$E$2:$E$3000,0))

(this occurs twice in my formula), and so on for I2 and J2.

Hope this helps.

Pete

Thanks Pete. Super long, but it gets all the names into one cell.