+ Reply to Thread
Results 1 to 7 of 7

CONCATENATE four cells defined by INDEX MATCH

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    41

    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.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: CONCATENATE four cells defined by INDEX MATCH

    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

  3. #3
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: CONCATENATE four cells defined by INDEX MATCH

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

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: CONCATENATE four cells defined by INDEX MATCH

    Are the values in G to J text or numbers?
    Glenn



  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: CONCATENATE four cells defined by INDEX MATCH

    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

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: CONCATENATE four cells defined by INDEX MATCH

    Maybe this:

    =SUBSTITUTE(TRIM(VLOOKUP(A2,E:J,3,FALSE)&" "&VLOOKUP(A2,E:J,4,FALSE)&" "&VLOOKUP(A2,E:J,5,FALSE)&" "&VLOOKUP(A2,E:J,6,FALSE))," ",", ")

    see sheet
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: CONCATENATE four cells defined by INDEX MATCH

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. INDEX and MATCH in defined range from another INDEX and MATCH
    By andy479 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-23-2019, 02:40 AM
  2. Match Index, Small Index with Concatenate
    By kharding15 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 05:38 PM
  3. Help Using Concatenate with Index Match
    By billyshears in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-25-2013, 10:09 AM
  4. [SOLVED] INDEX MATCH MATCH gives N/A without row numbers defined
    By cbearl78 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2013, 05:18 PM
  5. [SOLVED] Defined Name + Index Match
    By ricky2k2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-04-2012, 10:47 AM
  6. [SOLVED] INDEX-MATCH w/ CONCATENATE
    By zamgold in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-25-2012, 11:08 AM
  7. Replies: 4
    Last Post: 03-04-2010, 11:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1