Match info from one column and alter the text to something different in another column
I need to take the info from a column in a sheet called Snow Specific Annual Revenue, and then alter the text slightly so that its easier to sort in order...I have tried many iterations of Match/Index, most recent is:
=INDEX([Snow Specific Annual Revenue]:[Snow Specific Annual Revenue], MATCH([Snow Specific Annual Revenue]@row, {SNOW SPECIFIC REVENUE OPTIONS TO SORT Range 2}, 0))
The range in the secton part is a seperate sheet that has the 'new' values I need, which have A/B/C etc in front of them with a colon to sort them in order...see screen shot attached for the sheet, the column in yellow is where I need to new text to sit after it matches.
Here are the fields I'm trying to convert to more sortable data for charts etc.:
$0-$50,000 to A:$0-$50,000
$51,000 - $100,000 to B:$51,000 - $100,000
$101,000 - $250,000 to C:$101,000 - $250,000
$1 million - $2 million to F:$1 million - $2 million
Best Answer
-
Hi @brian25736
I hope you're well and safe!
The structure should look something like this.
=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))
-
Try something like this.
Example. (replace the last range with the ColumnWithTheValueToMatchAgainsTheCell
=INDEX({SNOW SPECIFIC REVENUE OPTIONS TO SORT Range 2}, MATCH([Snow Specific Annual Revenue]@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))
Make sense?
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @brian25736
I hope you're well and safe!
The structure should look something like this.
=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))
-
Try something like this.
Example. (replace the last range with the ColumnWithTheValueToMatchAgainsTheCell
=INDEX({SNOW SPECIFIC REVENUE OPTIONS TO SORT Range 2}, MATCH([Snow Specific Annual Revenue]@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))
Make sense?
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you so much, i appreciate it, it is working fine now!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!