Reference & Concatenation

Gavin Seaton

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

Kelly Moore

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

Kelly Moore

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

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

Gavin Seaton

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

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