Reference & Concatenation

Options

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Options

    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

  • Gavin Seaton
    Gavin Seaton ✭✭✭✭
    Options

    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

  • Gavin Seaton
    Gavin Seaton ✭✭✭✭
    Options

    Hi @Zachary Hall

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!