Can't compare two string values

So I'm using this formula to try and compare two "String" values. In this situation, some values in Column 2/3 will be numerical only and some will have text. I have a helper column checking that both are string values but it still won't match. I've tried with collect and contains too.

I'm trying to pull the value in column2 of Grid 1 onto Grid 2. Any help would be appreciated.

=INDEX({Grid 1 | Column 2}, MATCH([Column2]@row + "", {Grid 1 | Column3}))

Find more posts tagged with

Sheets

functionality

Cell linking

Accepted answers

Jeff Reisman

@Joseph Pardo The , 0 needs to be inside the two closing parentheses, not in between them.

Without the , 0, I can recreate your scenario exactly. Adding the ,0 at the right place solves it.

Without the , 0:

With the , 0:

All comments

Jeff Reisman

@Joseph Pardo I think you're missing the row index value at the end of your INDEX/MATCH. Try this, with , 0 placed after your range to match:

=INDEX({Grid 1 | Column 2}, MATCH([Column2]@row + "", {Grid 1 | Column3}, 0))

Joseph Pardo

https://community.smartsheet.com/discussion/comment/367723#Comment_367723

Thanks for the idea. I'm not sure why I forgot it on that formula, but I've tried it with that as well. I used 0 and 1.

Any other thoughts? I'm almost at the point where I don't think you can compare numerical values stored as strings.

Jeff Reisman

@Joseph Pardo The , 0 needs to be inside the two closing parentheses, not in between them.

Without the , 0, I can recreate your scenario exactly. Adding the ,0 at the right place solves it.

Without the , 0:

With the , 0:

Joseph Pardo

https://community.smartsheet.com/discussion/comment/367730#Comment_367730

This worked! Thanks so much, so now I have two separate formulas that I'm trying to turn into one.

I'm trying to get the value in Column2 (Grid1 or G1) by comparing Column2 value on G2, with Column3 value on G1. And checking If Test6 column value is contained in Column6 on G1. So basically trying to add in that match to the current formula I have.

=INDEX(COLLECT({Grid 1 | Column 2}, {Grid 1 | Column6}, CONTAINS([Test6]@row, @cell), [Column2]@row + "", {Grid 1 | Column3}), 1)

Any ideas here?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.