Combine 2 Index Match formulas to concatenate 2 cells?

Natalia Kataoka

I have 2 sheets, each with a column for Subcontract Codes and a String of all the jobs they have worked on. Sheet 1 lists jobs that we had positive experiences, Sheet 2 lists jobs we had negative experiences.

I need to combine the job numbers into a 3rd sheet, matching based upon the Sub Codes. I can easily get one list of jobs using an Index(Match formula, but I am running into problems combining 2 Index(Matches, or figuring out if this is best done with a different formula.

Sheet 1 - Positive

Sheet 2 - Neg

Destination Sheet:

I also cannot figure out why the "<BLANK>" and "don't have an approved..." are pulling in columns Company Details and Column3.

Ideas?

Natalia Kataoka

Thanks Jeff,

I just needed the Job No's combined (not comments), but I was able to do that using your syntax, final formula is:

=INDEX({Top Subs Pivot - String}, MATCH([Primary Column]95, {Top Subs Pivot - Code}, 0)) + "" + INDEX({Bottom Subs Pivot - String}, MATCH([Primary Column]95, {Bottom Subs Pivot - Code}, 0))

I also found the reason my Reviews were not pulling correctly- the pivots I was pulling from had updated and I did not extend my formulas to incorporate the new job no's in my strings.

Lastly, I had to leave the "=" signs in or the formula would not compute. I had found in another discussion in the community that there was some type of bug and the "=" remedied that.

Thanks!

Jeff Reisman

@Natalia Kataoka Can you share what formulas you're working with now?

Also I don't understand what you're asking with regards to "I also cannot figure out why the "<BLANK>" and "don't have an approved..." are pulling in columns Company Details and Column3." What do you mean by "pulling in columns"?

Getting back to your first question, concatenating multiple INDEX/MATCH formulas:

If your values are numbers, you'll want to convert the values to text, otherwise the system will add the values together (ex. 1234 + 2345 would be 3579.) By adding +"" at the end of each formula, you convert it to text value. So putting together two INDEX/MATCH formulas with a comma between them would look like this:

=INDEX({yada yada yada}, MATCH(shaggy@row, {scooby doo}, 0))+""+", "+ INDEX({OldManWilson}, MATCH(fred@row, {velmadaphne}, 0))

Natalia Kataoka

@Jeff Reisman

The formulas I am currently using:

String79: =INDEX({Top Subs Pivot - String}, MATCH([Company Details]3, {Top Subs Pivot - Code}, 0))

CompanyDetail79: =IFERROR(INDEX(COLLECT({Top Sub Comments Pivot - Comments}, {Top Sub Comments Pivot - Job No}, =[Primary Column]@row, {Top Sub Comments Pivot - Code}, [Primary Column]$78), 1), "")

Column3 79: =IFERROR(INDEX(COLLECT({Bottom Sub Comments Pivot - Comments}, {Bottom Sub Comments Pivot - Job no}, =[Primary Column]@row, {Bottom Sub Comments Pivot - Code}, [Primary Column]$78), 1), "")

Regarding the <BLANK> and "didn't have an approved...", I am just unsure why my formula is resulting in those entries. However, the "didn't have an approved..." is the correct result should I have had all my job numbers listed as I would like.

Jeff Reisman

The incorrect values may be because of these extraneous equal signs in your formulas. Get rid of them:

Your concatenated formula would look like this:

=INDEX({Top Subs Pivot - String}, MATCH([Company Details]3, {Top Subs Pivot - Code}, 0))+""+", "+ IFERROR(INDEX(COLLECT({Bottom Sub Comments Pivot - Comments}, {Bottom Sub Comments Pivot - Job no}, [Primary Column]@row, {Bottom Sub Comments Pivot - Code}, [Primary Column]$78), 1), "")+""+", "+IFERROR(INDEX(COLLECT({Bottom Sub Comments Pivot - Comments}, {Bottom Sub Comments Pivot - Job no}, =[Primary Column]@row, {Bottom Sub Comments Pivot - Code}, [Primary Column]$78), 1), "")

Natalia Kataoka

Thanks Jeff,

I just needed the Job No's combined (not comments), but I was able to do that using your syntax, final formula is:

=INDEX({Top Subs Pivot - String}, MATCH([Primary Column]95, {Top Subs Pivot - Code}, 0)) + "" + INDEX({Bottom Subs Pivot - String}, MATCH([Primary Column]95, {Bottom Subs Pivot - Code}, 0))

I also found the reason my Reviews were not pulling correctly- the pivots I was pulling from had updated and I did not extend my formulas to incorporate the new job no's in my strings.

Lastly, I had to leave the "=" signs in or the formula would not compute. I had found in another discussion in the community that there was some type of bug and the "=" remedied that.

Thanks!

Natalia Kataoka

@Jeff Reisman Maybe I spoke too soon. My formula seems to be working for the most part, but I am finding a couple of odd things are happening that I cannot remedy.

The concatenated formula is returning one job number that is not present in either of the 2 strings I am combining.

Sheet 1 - search value is BAYST

Sheet 2 - the search value is not present in this sheet, and there is only one other row referencing the extraneous job number. Is it attempting to pull the closest match?

Second odd item is I cannot replicate the formulas under Positive & Negative onto any other sheet, each time they pull #INVALID REF.

Jeff Reisman

@Natalia Kataoka

I'm can't tell why your formula would be pulling in 20426... Split your formulas apart into stand-alone functions in some test columns and see if you can piece it together.

As far as the #INVALID REF error - when you copy a formula from one sheet to another, you have to recreate the cross-sheet references. References are specific to the sheet they were created in. So edit your formula, click on the reference, and the use the Edit the Reference link to set it for the new sheet.

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--invalid-ref-

#INVALID REF 

Cause

A reference name to another sheet (in curly braces) doesn’t exist as a reference to another sheet.

Resolution

Either change the name within the curly braces to be that of an already created reference to another sheet, or create a new reference to another sheet.

See Formulas: Reference Data from Other Sheets for more information on this process.

Natalia Kataoka

@Jeff Reisman

Ok, splitting the formula was helpful in solving my errors! Wrapping each portion of the formula in an IFERROR and changing the search type on the second formula to 'unsorted' fixed the extraneous jobs (though I am not sure why, the sheet it is searching is sorted ascending).

Jeff Reisman

@Natalia Kataoka Glad you got it fixed. I tend to default towards unsorted, I usually have the best luck with that.