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
-
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.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!