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.
Bookmarks