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}))
Best Answer

@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:
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

@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))
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

@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:
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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?
Help Article Resources
Categories
Check out the Formula Handbook template!