Combine 2 Index Match formulas to concatenate 2 cells?
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?
Best Answer

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!
Answers

@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))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

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), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

I'm can't tell why your formula would be pulling in 20426... Split your formulas apart into standalone 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 crosssheet 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.
#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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

@Natalia Kataoka Glad you got it fixed. I tend to default towards unsorted, I usually have the best luck with that.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!