Is it possible for VLOOKUP or INDEX/MATCH TO combine two cells

I am using the following formula that display course terms =JOIN(COLLECT({term}, {course}, "2022", {id}, $ID@row), CHAR(10)) and it works great and displays results like this

I would like to amend the formula to include the course number in addition to the term to display something like this "202103-6220"

I was only successful in doing this:

=JOIN(COLLECT({term}, {course}, "2022", {id}, $ID@row), CHAR(10)) + JOIN(COLLECT({course}, {term}, "2022", {id}, $ID@row), CHAR(10))

which gave me output

As you see in instances where there are multiple terms, the first join collect formula goes through entire cycle and puts those terms up first, then executes the second join collect formula to append the course which is not what I would like. Any way to rewrite ?

Find more posts tagged with

Accepted answers

All comments

Mike TV

@gwson

You can either turn word wrap on for the column which will then allow your CHAR(10) parts of the formula to drop the next part down a line, or you can add another + "-" + in the middle of your formula to put in the dash like in your example.

Mike TV

@gwson

Did that not work for you or do you need help in another direction? Turning word wrap on allows your CHAR(10) to drop it down a line but without word wrap that will basically be ignored. Adding the + "-" + in the places you need a dash will break up the numbers into the format you're looking for.

gwson

The issue is not with the word wrap, but with how my current formula works. I am joining two loop formulas and they execute one at a time for the entire loop, instead of at the same time.

If there are multiple matching values, the first join collect goes through the entire column and grabs them i.e

202203

202202

202001

then proceed to execute the second loop for 2nd criteria and adds them below i.e

6220

I need the formula to be something like this:

=JOIN(COLLECT({term}+"-"+{course}, {course}, "2022", {id}, $ID@row), CHAR(10))

in order to get output that looks like this:

202203-6220

202202-6220

202001-6220

In other words, I want join collect to look in two different columns, combine the values from those columns if they match year "2022".

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.