Reference & Concatenation
Hi
Struggling to get my head around converting Excel to Smartsheet formulas.
I have an excel formula that matches cells on different tabs, then concatenates the match with details from another cell like this:
=XLOOKUP(F2,'EWC Codes'!B:B,CONCATENATE('EWC Codes'!C:C," (",'EWC Codes'!B:B,") ")," ")
On Smartsheet the information is on 2 different sheets and i have tried this and many other formulas that dont work:
=INDEX({Waste Reporting EWC Codes Range 1}, MATCH([EWC CODE*]@row, {Waste Reporting EWC Codes Range 1}, JOIN({Waste Reporting EWC Codes Range 2}:[EWC CODE*]@row,") ")," ")
Gavin
Best Answer
-
Hey @Gavin Seaton
If I understand you, you want to Match a result from another sheet, then concatenate that result with another cell on your destination sheet. Is that correct? I am confused by your second range reference - I don't think that column is shown in your screenshot above? (As a note, you can rename your cross sheet references to reflect the name of the column/range you are referring to)
I'm wondering if you're trying for this:
=INDEX({Waste Reporting EWC Codes Range 1}, MATCH([EWC CODE*]@row, {Waste Reporting EWC Codes Range 1},0))+INDEX(Waste Reporting EWC Codes Range 2},MATCH([EWC CODE*]@row,{Waste Reporting EWC Codes Range 1},0))
(smartsheet can concatenate with a + sign)
Does this get you close to what you are looking for?
Kelly
Answers
-
Hey @Gavin Seaton
If I understand you, you want to Match a result from another sheet, then concatenate that result with another cell on your destination sheet. Is that correct? I am confused by your second range reference - I don't think that column is shown in your screenshot above? (As a note, you can rename your cross sheet references to reflect the name of the column/range you are referring to)
I'm wondering if you're trying for this:
=INDEX({Waste Reporting EWC Codes Range 1}, MATCH([EWC CODE*]@row, {Waste Reporting EWC Codes Range 1},0))+INDEX(Waste Reporting EWC Codes Range 2},MATCH([EWC CODE*]@row,{Waste Reporting EWC Codes Range 1},0))
(smartsheet can concatenate with a + sign)
Does this get you close to what you are looking for?
Kelly
-
Gavin,
If I understand correctly you are wanting to join data from another sheet with data on your current sheet. If the data you are joining is text the following formula may be what you are looking for. Feel free to reply if I've misunderstood.
=INDEX({Waste Reporting EWC Codes Range 1}, MATCH([EWC CODE*]@row, {Waste Reporting EWC Codes Range 1},0)) + [EWC CODE*]@row
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
Hi @Kelly Moore
Thanks for the reply, this works just how i need it to 👍️.
Sorry about the second range reference confusion, I hadn't included that on my images.
Gavin
-
Thanks for responding, appreciate it. The formula didnt quite work as needed but as Kelly had pointed out I wasn't very clear on what i was looking for.
Gavin
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 211 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!