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 ?

Answers

  • Mike TV
    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
    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
    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".

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!